16 April 2012 14:58
I have a table productionheader, in which I have a column Status. I want to create an Update trigger when status =5. I am trying following code. It's not working.
EXISTS (SELECT name FROM sysobjects
WHERE name = 'trgStatusChange' AND type = 'TR')
DROP TRIGGER trgStatusChange
CREATETRIGGER trgStatusChange ON dbo.ProductionRunHeaders
ASIF update(Status = 5)
EXEC [dbo].[spProductionRunYieldSummary] 1022
16 April 2012 15:04check the status value from the inserted table instead of what you are doing current
Thanks and regards, Rishabh , Microsoft Community Contributor
16 April 2012 15:06
Try this. However, when do you want to fire the SP, when at least one row updates? What is the exact business case?
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trgStatusChange' AND type = 'TR') DROP TRIGGER trgStatusChange GO CREATE TRIGGER trgStatusChange ON dbo.ProductionRunHeaders For UPDATE AS IF update(Status) Begin IF EXISTS(SELECT * FROM inserted WHERE Status = 5) BEGIN EXEC [dbo].[spProductionRunYieldSummary] 1022 END END go
Abdallah El-Chal, PMP
16 April 2012 15:06
1) IF inserted.status = 5 is what you are looking for
2) You need to deal with the case where multiple rows were updated so your method isn't going to work - trigger fires once per update not once per row
There is a cursor example that would work for you here:
16 April 2012 15:07Moderator
UPDATE function only checks if the field was changed or not, e.g.
if exists (select 1 from Inserted where Status =5 ) -- some of the rows now have status = 5
execute dbo.spProductionRunYieldSummary 1022
For every expert, there is an equal and opposite expert. - Becker's Law
16 April 2012 15:08
CREATE TRIGGER TRGSTATUSCHANGE ON DBO.PRODUCTIONRUNHEADERS AFTER UPDATE AS BEGIN DECLARE @TSTATUS NUMERIC SELECT @TSTATUS=STATUS FROM INSERTED IF @TSTATUS=5 BEGIN PRINT 'I CAME COME HERE' --EXEC [DBO].[SPPRODUCTIONRUNYIELDSUMMARY] 1022 END END -- Test CREATE TABLE PRODUCTIONRUNHEADERS (ID INT, VAL CHAR(100),STATUS INT) INSERT INTO PRODUCTIONRUNHEADERS VALUES(1,'AAA',1) INSERT INTO PRODUCTIONRUNHEADERS VALUES(2,'BB',1) SELECT * FROM PRODUCTIONRUNHEADERS UPDATE PRODUCTIONRUNHEADERS SET STATUS=5 WHERE ID =1
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
16 April 2012 15:30
Thanks Naomi, this script is working and I am getting a result as I want.