Trigger stored procedure on status update

Question

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.

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 = 5)

Begin

EXEC [dbo].[spProductionRunYieldSummary] 1022

END

 go

---

kmp

Answer 1

check the status value from the inserted table instead of what you are doing current

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

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

Answer 3

Couple things

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:

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/40f5635c-9034-4e9b-8fd5-c02cec44ce86

---

Chuck

Answer 4

UPDATE function only checks if the field was changed or not, e.g.

IF UPDATE(Status)

  BEGIN

  if exists (select 1 from Inserted where Status =5 ) -- some of the rows now have status = 5

    execute dbo.spProductionRunYieldSummary 1022

  END

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Kevin,

Try 

 

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

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 6

Thanks Naomi, this script is working and I am getting a result as I want.

---

kmp