Executing Trigger
Nov 28, 2007
Hi Everybody,
I am new to ms sql server i had small proublem
i am created Trigger it will work so fine command successfully
how can i executed that Trigger plz help
Regards
subu
ADVERTISEMENT
Executing DDL In A Trigger
Nov 15, 2000Dear All,
I would like to execute a DDL statement(create table) in a trigger. The DDL statement is stored as a field in another table. I have read that sql statement into a variable of type varchar. How do I execute that sql statement.
Thanks in advance
Trigger Not Executing
Aug 13, 2006I will try to keep this as simple as possible and make a long story short. The college I work for has bought a third party package to basically run the operations of the school. There is a component for registration, admissions, billing, etc. It also has a web component so students can access their school information from anywhere. The backend for all of this is SQL Server. To make things easy, the vendor has provided us with the steps to bulk load users, via DTS, into the system and create their accounts for the web portion. The way this works is as follows:
I create a CSV file which I then load via DTS into their "Bulk Load" table. This table has an "on insert" trigger that fires off when the load happens on the "Bulk Load" table. The trigger verifies that the user exists in the system, encrypts the password I provide in the file and inserts the record into an "Accounts" table. This "Accounts" table is where the system checks when a user logs in. Everything works fine on our test server, but in live, the trigger does not execute. The DTS package executes and the "Bulk Load" table gets updated, but the "Accounts" table does not. My file has 382 valid students and again, it worked fine on the test server.
Differences between the servers:
1. The DTS packages are the same, except for the server they connect to.
2. The test server is stand alone, while the production server is clustered.
What I tried:
1. A ran the alter statement to enable the trigger thinking it might be disabled.
2. Dropped the trigger on production and re-created it with the trigger from our test server.
3. Tried to run a trace (I had never done that before). Noticed some sp's running, but none of them were sp's found in the trigger.
Any thoughts or ideas as to what I can check? Do triggers have certain permissions and if so, where can I make sure I have the permissions. I should have full access under my account as should the account that SQL Server Agent is logged in as.
Thanks for any help.
Pete
Sql Command Executing Trigger
Jan 5, 2004hi.
I must get i trigger the sql command, which execute trigger.
SQL 2012 :: Executing SP From Trigger?
Mar 21, 2014Below is my requirement..
Create trigger on Table_XYZ ( After Insert,After Update)
select @var=Col_Last field is null from inserted
if @var is null
Execute sProc1
create proc sproc1
Business logic will create two csv files. Success and error files
success record will be deleted from Table_XYZ,
Error records updated with error_message.
Problem:- When i am updating Col_Last value to null or insert a record with NULL for Col_Last
Trigger is firing and executing SP.....But i was not able query the Table_XYZ and taking too much time...
sp_who2 shows blocked by...my SPID only
when i am executing SP alone ...every thing works fine....
when i am doing testing on trigger with Insert/Update... i am getting executinf query... no response...
If i comment below ones in Trigger...working fine...
--if @var is null
--Execute sProc1
Executing A Views From A Trigger
Mar 1, 2006Hi,I am new to using SQL. I want to be able to exucute a query that Iplace in a view. I want this views to be executed every time a valuechange in one of the table in a particular field. So my guess was touse a trigger that will call the views every time the data change inthe selected table. Is this the proper way of doing thing? Should Iuse other SQL tools to achive this. I search for exemple of triggerexecuting views but did not found anything as of yet. Let's use thisdummy name for the exemple:Database: DB1Table: Tbl1Special field in Tbl1: flagViews name: views_01Thank you.Philippe
View 7 Replies View RelatedTrigger Not Executing CLR Code/stored Proc
May 7, 2008I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -
I have CLR integration enabled on the sql server.
The dbo has UNSAFE ASSEMBLY rights
I have the both the assembly and the serialized dll imported in the database.
Here's the definition of the stored proc -
CREATE PROCEDURE [dbo].[WriteXMLNotification]
@TaskID [nvarchar](20)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]
and the trigger -
CREATE TRIGGER [dbo].[tr_Task_U]
ON [dbo].[_Task]
FOR UPDATE, INSERT AS
IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)
BEGIN
SET NOCOUNT ON;
DECLARE @status AS INT
DECLARE @taskType AS INT
DECLARE @taskSubType AS INT
DECLARE @taskID as sysname
DECLARE @cmd as sysname
DECLARE @parentTask as sysname
DECLARE @NotificationXMLTaskID as sysname
SELECT @status = [TaskType_Status] FROM inserted
SELECT @taskType = [TaskType] FROM inserted
SELECT @taskSubType = [TaskType_SubType1] FROM inserted
SELECT @taskID = [TaskID] FROM inserted
SELECT @parentTask = [Parent_TaskID] FROM inserted
SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask
IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)
BEGIN
exec WriteXMLNotification @taskID;
END
ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)
BEGIN
exec WriteXMLNotification @parentTask;
END
END
I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?
Please advise!
Thanks in Advance,
-Mihir Sonalkar.
Executing Store Procedre By Calling It From An Insert Trigger?
Feb 10, 2000hi, I have a trigger on a table for insert, once there is new data into that table I want to run a nother store procedure by passing all input from inserted to the store procedure as input parameter. can I do that.
Thanks
Ali
Trigger Executing Linked Server Stored Procedure
Jul 3, 2006What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
Thank you
Transact SQL :: Executing Stored Procedure Within Trigger Failing But Separate Works
Nov 4, 2015I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:--
CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVERÂ
AFTER CREATE_DATABASE
ASÂ
--execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
SQL Server Admin 2014 :: Logon Trigger Executing Multiple Times For Single Connection?
Jan 30, 2015I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Jan 23, 2008Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedDifferent Results When Executing From .NET Component Compare To Executing From SQL Management Studio
Oct 10, 2006Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedCLR-Based Trigger? Recursive Trigger? Common Table Expression?
Nov 14, 2006Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
Trouble With Update Trigger Modifying Table Which Fired Trigger
Jul 20, 2005Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedGeneric Audit Trigger CLR C#(Works When The Trigger Is Attached To Any Table)
Dec 5, 2006This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
Oct 30, 2007Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
Feb 5, 2008A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
How To Create New CLR Trigger From Existing T-Sql Trigger
Mar 18, 2008how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies View RelatedModifing The Row That Invokes A Trigger From Within That Trigger
Jul 23, 2005When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.
View 4 Replies View RelatedDisabilitazione Trigger [DISABLE TRIGGER]
Jul 20, 2005Salve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.
View 4 Replies View RelatedUpdate Trigger Behaviour W/o A Trigger.
May 30, 2008Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
Drop Trigger With A Variable Trigger Name
Sep 20, 2007
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?
thanks in advance
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER RemoveTriggers
ON tblTriggers
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @DeleteTrigger as nvarchar(max)
select @DeleteTrigger = TableName FROM DELETED
IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL
DROP TRIGGER @DeleteTrigger
GO
END
GO
Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null
Jan 23, 2008
Hi,
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
Pb Executing An SP
Sep 25, 2006I have an SP that has this part of code located in the line:30
select convert(datetime,CONFIGURATION1) from dbdw..configuration
where CLECONFIG = 'DATE-AJOUT-VENTES'
I get teh error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Do u know what the problem is pls
Executing A Job Using VB
Sep 17, 2007I want to execute a backend SQL job using VB on an MSACCESS frontend.
I know how to run a DTS using VB script. Can anyone give me the syntax for a SQL job? Thanks in advance.
Executing A DTS From Another DTS...
Jul 20, 2005Hi there,I am trying to execute a DTS package from another DTSpackage and change some variables in the original DTS. Ihave used the info on the following page but it doesn'twork: http://www.sqldts.com/default.aspx?215. Would youhave any input?Thanks,Andreas*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedExecuting More Than One Query
Jun 16, 2007Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
Executing DTS (on Sql Server) From ASP.NET??
Feb 5, 2004Does Any one know how to execute DTS (on sql server) from ASP.NET??
View 2 Replies View RelatedExecuting DTS Package From VB.NET
Nov 16, 2004Hello,
I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:
Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)
I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?
Any ideas?
Brian
Job Sticks On "executing"
Jan 18, 2001Hi, I have a DTS package that runs correctly when executed it manually, but when I schedule a job via the package, the job begins executing, but does not stop. I have to cancel it. The Package runs in less that 1 minute, but the job reports that it's being executed but does not stop until I cancel it.
The job was created by using the DTS package schedule command, so how can I check what's wrong?
Thanks in advance.
Executing SP In Loop
Sep 21, 2000What is way for executing SP in loop? I have to delete some records in table X depends on records from table Y. Simple way is:
DELETE FROM X
WHERE X.ID IN
(
SELECT ID FROM Y WHERE ...
)
But if deleting from X is not so simply (after deleting from X I have to delte it from 5 others tables) ? I've created SP 'SP_DELETE_X' which has input parameter 'ID'. My question is how execute this SP in above loop???
Fly