Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert trigger not working when update trigger enabled


Insert trigger not working when update trigger enabled

Author
Message
gdavidson-740471
gdavidson-740471
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57043 Visits: 44707
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, MVP, M.Sc (Comp Sci)
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


gdavidson-740471
gdavidson-740471
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
noeld
noeld
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6970 Visits: 2048
Do you have "Recursive Triggers" Enabled ?


* Noel
gdavidson-740471
gdavidson-740471
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
gdavidson-740471
gdavidson-740471
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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() ''
gdavidson-740471
gdavidson-740471
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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 <> '' 


bijayanix24
bijayanix24
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search