March 17, 2009 at 9:13 am
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.
[font="Courier New"]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
[/font]
March 17, 2009 at 9:24 am
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
March 17, 2009 at 9:52 am
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.
March 17, 2009 at 10:25 am
Do you have "Recursive Triggers" Enabled ?
* Noel
March 17, 2009 at 11:38 am
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.
March 17, 2009 at 11:40 am
something appears to have gone adrift when I cut and pasted the code the line with trigger nestlevel should read:
if trigger_nestlevel() ''
March 17, 2009 at 11:49 am
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 <> ''
February 12, 2010 at 12:04 am
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'
)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply