CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Idrees Khan
Idrees Khan

Reputation: 7752

SQL Server After Update Trigger Not Working

i have schema with the following structure; enter image description here
In MPRMain table, the Status can be 1(Aprove), 2(Cancel) and 3(Pending). I'm trying to do something like this e.g When someone update the Status (in MPRMain) to 2(Cancel). I want to Update the Status of a MPRDetail table to 0 (0 for disable and 1 for enable).
So, i have tried to create a trigger for it which looks like this;

Alter Trigger Inventory.MprMainUpdate
    ON Inventory.MPRMain
    AFTER UPDATE
AS
BEGIN 

    declare @status as int;
    set @status = (SELECT Status FROM inserted);
    if(@status=2)
    BEGIN
        UPDATE Inventory.MPRDetail
        SET Status = 0   -- update the status to canceled
        WHERE MPRId = (select MPRId from inserted);
    END

END

However, When i try to Set the Status of a MPRMain to 2 (Cancel) From 1 (Aprove), i don't see any changes in MPRDetail table. This should update the MPRDetail Status to 0 (0 for disable).

Upvotes: 3

Views: 9610

Answers (1)

marc_s
marc_s

Reputation: 754518

Since Inserted can contain multiple rows, you need to write the trigger with a set-based approach:

ALTER TRIGGER Inventory.MprMainUpdate
    ON Inventory.MPRMain AFTER UPDATE
AS
BEGIN 
  UPDATE Inventory.MPRDetail
  SET Status = 0
  FROM Inserted i
  WHERE i.Status = 2 AND Inventory.MPRDetail.MPRId = i.MPRId;
END

So basically you need to join your actual data table against the Inserted pseudo table based on the MPRId column, and update all rows in your base table to Status = 0 where Inserted.Status = 2

OK - here I go: assuming your UPDATE statement updates four rows, then Inserted might look like this in your trigger:

MPRId  MprNo  Date  DepartmentId  Status
  1      42     ..    .......        1
  2      43     ..    .......        2
  7      33     ..    .......        7
  9      41     ..    .......        2

Now, you JOIN this set of data against your MPRDetail table and you only pick those rows where the Status in Inserted is 2 - so you get

MPRId  Status  MPRDetail.Status  (other columns of MPRDetail for those values)
  2      2           4           .....................................
  9      2           5           .....................................

So in this case, both those rows in MPRDetail would have their Status updated to 0 inside the trigger - any other rows will not be touched.

Does that make things a bit clearer? If not: where are you stuck, what's still "magic" and thus unclear to you?

Upvotes: 2

Related Questions