InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:30:05 GMT20http://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxSometimes we need to audit all DML operations for tables in a MSSQL database. There are many methods for achieving this, one of the most common approaches is using DML Triggers . But DML triggers can be expensive so we can make use of the OUTPUT clause which is a new TSQL feature available in SQL SERVER 2005 and later. The OUTPUT clause can make use of the INSERTED and DELETED virtual tables just like Triggers. We can use output clause with a table variable , temporary table or a permanent table. Some examples are given below: OUTPUT clause with INSERT statement--------Creating the primary table which will store data CREATE TABLE TestTable (ID INT, FIRSTNAME VARCHAR(100)) ----Declaring a TABLE VARIABLE to store values of OUTPUT clause DECLARE @TmpTable TABLE (ID INT,FIRSTNAME VARCHAR(100))----Insert values in real table as well use OUTPUT clause to insert ----values in the table variable. INSERT TestTable (ID, FIRSTNAME)OUTPUT Inserted.ID, Inserted.FIRSTNAME INTO @TmpTableVALUES (1,'FirstVal') -----OUTPUT clause into Table with UPDATE statement --------Creating the primary table which will store data CREATE TABLE TestTable5 (ID INT, FIRSTNAME VARCHAR(100 )) ----Declaring a TABLE VARIABLE to store values of OUTPUT clause DECLARE @TmpTable TABLE (ID_New INT, FirstName_New VARCHAR(100),ID_Old INT, FirstName_Old VARCHAR(100 )) ----Insert values in real table as well use OUTPUT clause to insert----values in the table variable. INSERTTestTable5 (ID, FIRSTNAME )VALUES(1,'Ari' )INSERTTestTable5 (ID, FIRSTNAME )VALUES(2,'Ari1' )[url=http://www.mindfiresolutions.com/Avoiding-the-use-of-Triggers-in-SQL-Server-165.php]Eliza[/url]Fri, 12 Feb 2010 00:04:47 GMTbijayanix24http://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxsomething appears to have gone adrift when I cut and pasted the code the line with trigger nestlevel should read:[code] if trigger_nestlevel() < 2 AND @EventTemplateID = @TemplateID AND @SRno <> '' [/code]Tue, 17 Mar 2009 11:49:27 GMTgdavidson-740471http://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxsomething appears to have gone adrift when I cut and pasted the code the line with trigger nestlevel should read:if trigger_nestlevel() ''Tue, 17 Mar 2009 11:40:28 GMTgdavidson-740471http://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxYes - but I am using the trigger with a third party applications database so I am not sure that I can turn off recursive triggers as that is a global setting. Hence the use of trigger_nestlevel to check the recursion depth.Tue, 17 Mar 2009 11:38:26 GMTgdavidson-740471http://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxDo you have "Recursive Triggers" Enabled ?Tue, 17 Mar 2009 10:25:44 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxThis table should only be updated one row at a time. Since I'm making my way with SQL server I'm trying to keep things simple, but I agree that I should change it to be able to handle multiple inserts/updates later.Tue, 17 Mar 2009 09:52:13 GMTgdavidson-740471http://www.sqlservercentral.com/Forums/Topic677582-338-1.aspx[quote][b]gdavidson (3/17/2009)[/b][hr]set @CRno = (SELECT IEIncidentID FROM inserted)set @EventID = (SELECT IEventID FROM inserted)set @EventTitle = (SELECT IETitle FROM inserted)set @EventTemplateID = (SELECT IETemplateID FROM inserted)[/quote]What's going to happen when more than one row is inserted/updated in a statement and the inserted table contains multiple rows?Tue, 17 Mar 2009 09:24:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic677582-338-1.aspxI have a trigger that inspects an NVARCHAR and, if it determines that the data at the start of the field looks like the key for a particular table in another linked database, executes a query using that key to get a customer name and replaces the original value of the field with the key followed by the customer name.This action needs to be performed when a new row is inserted, and when the user updates an existing row, in case he changes the key.First I created an INSERT trigger - this worked. Then I changed the trigger to work for UPDATE - I could then change the key value and watch the customer name change. So far so good. Then I retested the INSERT of a row, nothing - no insert performed. Then I disabled the UPDATE trigger and the INSERT worked.I thought that the problem might be caused by the UPDATE trigger getting fired by the update performed during INSERT and tried to prevent that occurring by use of the trigger_nextlevel function.The source for the trigger is below - can anyone tell me how to resolve this problem -ThanksGiles.[font="Courier New"]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [tr_getcust] on [dbo].[Incident] FOR INSERT, UPDATEASdeclare @Project INTdeclare @SRno NVARCHAR(10)declare @CRno NVARCHAR(10)declare @EventID INTdeclare @EventTitle NVARCHAR(500)declare @Desc NVARCHAR(500)declare @EventTemplateID INTdeclare @TemplateID INTset @Project = 30set @CRno = (SELECT IEIncidentID FROM inserted)set @EventID = (SELECT IEventID FROM inserted)set @EventTitle = (SELECT IETitle FROM inserted)set @EventTemplateID = (SELECT IETemplateID FROM inserted)set @TemplateID = 15set @SRno = dbo.RegExMatch(@EventTitle,'1-\d\d\d\d\d*')if trigger_nestlevel() '' Begin declare @query NVARCHAR(500); declare @parm NVARCHAR(500); declare @Customer NVARCHAR(500) declare @Custproject NVARCHAR(20) set @parm = N'@cust VARCHAR(50) OUTPUT, @proj VARCHAR(50) OUTPUT'; set @query = 'SELECT @cust = NAME, @proj = PROJ_NUM FROM openquery(CRMTest, ''SELECT P.NAME,P.PROJ_NUM FROM CRM.SRV_REQ S, CRM.PROJ P WHERE p.row_id=s.proj_id AND s.sr_num='''''+@SRno + ''''' '')'; execute sp_executesql @query, @parm, @Cust = @customer OUTPUT, @Proj = @custproject OUTPUT; if @customer is NULL set @customer = 'No customer or SR not found' set @EventTitle = @SRno + ' - ' + @customer; UPDATE IncidentEvent SET IETITLE = @EventTitle WHERE IEventID = @EventID AND IETemplateID = @TemplateID AND IEProjectID = @project AND IEIncidentID = @CRnoEnd[/font]Tue, 17 Mar 2009 09:13:52 GMTgdavidson-740471