Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert trigger not working when update trigger enabled Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 8:34 AM
Points: 8, Visits: 23
I 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 -
Thanks
Giles.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tr_getcust] on [dbo].[Incident] FOR INSERT, UPDATE
AS

declare @Project INT
declare @SRno NVARCHAR(10)
declare @CRno NVARCHAR(10)
declare @EventID INT
declare @EventTitle NVARCHAR(500)
declare @Desc NVARCHAR(500)
declare @EventTemplateID INT
declare @TemplateID INT

set @Project = 30
set @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 = 15

set @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 = @CRno
End


Post #677582
Posted Tuesday, March 17, 2009 9:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
gdavidson (3/17/2009)

set @CRno = (SELECT IEIncidentID FROM inserted)
set @EventID = (SELECT IEventID FROM inserted)
set @EventTitle = (SELECT IETitle FROM inserted)
set @EventTemplateID = (SELECT IETemplateID FROM inserted)


What's going to happen when more than one row is inserted/updated in a statement and the inserted table contains multiple rows?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #677597
Posted Tuesday, March 17, 2009 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 8:34 AM
Points: 8, Visits: 23
This 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.
Post #677631
Posted Tuesday, March 17, 2009 10:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
Do you have "Recursive Triggers" Enabled ?



* Noel
Post #677677
Posted Tuesday, March 17, 2009 11:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 8:34 AM
Points: 8, Visits: 23
Yes - 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.
Post #677738
Posted Tuesday, March 17, 2009 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 8:34 AM
Points: 8, Visits: 23
something appears to have gone adrift when I cut and pasted the code the line with trigger nestlevel should read:

if trigger_nestlevel() ''
Post #677743
Posted Tuesday, March 17, 2009 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 8:34 AM
Points: 8, Visits: 23
something appears to have gone adrift when I cut and pasted the code the line with trigger nestlevel should read:

 if trigger_nestlevel() < 2 AND @EventTemplateID = @TemplateID AND @SRno <> '' 

Post #677751
Posted Friday, February 12, 2010 12:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 2:18 AM
Points: 15, Visits: 21
Sometimes 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 @TmpTable
VALUES (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.

INSERT
TestTable5 (ID, FIRSTNAME
)
VALUES
(1,'Ari'
)
INSERT
TestTable5 (ID, FIRSTNAME
)
VALUES
(2,'Ari1'
)

Eliza


Cheers,
Bijayani
Proud to be a part of Team Mindfire.

Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
Post #864463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse