Trigger to update column in a second table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to update column in a second table

Author

Topic

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-06 : 13:49:03

I need help to create a trigger that, when a specific column in table A is updated a column in Table B gets updated with a certain value.

I have 3 tables:

Messages (ID int, Name varchar)
Delivery (ID, Flag1 int, ...)
Product (ID, Flag1,...)

Those Flag fields refer to the ID in "Messages".

Now when the Flag1 in Delivery is set to 1 I want the Flag1 in Product to be set to 2.

Do I need to work with INSERTED, DELETED here?

I can't seem to figure this out - I hope my code is not that bad - but triggers, I don't get yet.


create TRIGGER [dbo].[tr_UpdateStatusFlag2]
ON [dbo].[Delivery]
FOR INSERT, UPDATE
AS
IF UPDATE (Flag1)
Begin

DECLARE @FLAG int

Select @FLAG = Flag1 FROM Delivery

IF @FLAG = 1 
BEGIN
	UPDATE Product
	SET Flag1 = 2

FROM Delivery D, Product P 
WHERE D.ID = P.ID 
END
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 14:03:34

You don't want to use a variable because the insert/delete could be more that one row so yes, you need to use the virtual tables inserted and deleted.

try this:


update p set
       p.Flag1 = 2
from   inserted i
inner join product p
       on d.id = i.id
left outer join deleted d
       on d.id = i.id
where  i.Flag1 = 1                    --new value of Delivery.flag1 = 1
and    i.Flag1 != isNull(d.Flag1, 0)  --Delivery.flag1 was updated EDIT: or was a new inserted value
and    p.Flag1 != 2                   --product.flag1 isn't already 2

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 14:05:40

not tested...but you should look into using the virtual tables


CREATE TRIGGER [dbo].[tr_UpdateStatusFlag2]
ON [dbo].[Delivery]
FOR INSERT, UPDATE
	UPDATE Product p SET Flag1 = 2
	WHERE EXISTS (SELECT * 
					FROM inserted 
				   WHERE Flag1 = 1 
					 AND p.[ID] = i.[ID])

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-06 : 14:30:52

wow that was quick and it works too :)

Only had to change the typo d.id to p.id

from   inserted i
inner join product p
       on d.id = i.id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 14:40:18

oh yeah - good catch. I think i originally joined to Delivery so the alias was [d] and never changed back when I corrected the table to be product.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 14:46:05

why bother joining to deleted?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 14:55:49

I believe the OP originally said that he only wanted to perform the update if the Flag1 column was "updated" So I just used deleted to see if the value was either a new or different value.

Be One with the Optimizer
TG

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-06 : 15:16:55

one more question: regarding TG's solution
let's say I want to check for different updates.
So if Delivery.Flag1 = 1 then I want to set product to 2 but if Delivery.Flag1 = 2 I want to set product to 3 ?

I suppose "if" or "case" would work but how (what's the right structure)?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 15:22:57

That would be a CASE statement. Of course you would need to modify the WHERE clause as well. Now if your example is accurate you may not need a case statement but a simple expression of Flag1+1 - but maybe that was just a bogus example.

Be One with the Optimizer
TG

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-06 : 15:41:58

would you be so kind and show me with the example how that case statement works and how the Where clause needs to be modified?


ALTER TRIGGER [dbo].[tr_UpdateStatusFlag4] ON [dbo].[Delivery]
    FOR INSERT, UPDATE
AS
    BEGIN
        UPDATE  p
        SET     p.Flag1 = 2
        FROM    inserted i
                INNER JOIN product p ON p.id = i.id
                LEFT OUTER JOIN deleted d ON d.id = i.id
        WHERE   i.Flag1 = 1                    --new value of Delivery.flag1 = 1
            AND i.Flag1 != ISNULL(d.Flag1, 0)  --Delivery.flag1 was updated EDIT: or was a new inserted value
          AND p.Flag1 != 2                   --product.flag1 isn't already 2
    END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 15:54:42

did you check out the CASE hyperlink in my previous post? It has plenty of syntax samples. Give it a try first...

The where clause would just need to change (i.Flag1=1) to (i.Flag in (1,2,etc...)
and (p.Flag1 != 2) would need to either be removed or changed to (p.Flag1 != <case expression>)

Be One with the Optimizer
TG

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-06 : 15:58:13

oh sorry didn't see that link - eyes are hurting from all the sql :)
I will check it out and might ask for help tomorrow again :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 16:07:18

Feel free - good luck - give your sql-sore eyes a rest.

Be One with the Optimizer
TG

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-07 : 14:42:51

I'm back :)
I've tried but I don't get this syntax. I hope I'm somewhere near the solution, a little help would be very appreciated.


  BEGIN
        UPDATE  p
        
                SET p.Flag1 = 
				 CASE   
			        	WHEN (i.Flag1 = 1) THEN p.Flag1  = 2
					WHEN (i.Flag1 = 2) THEN  p.Flag1 = 5
					FROM    inserted i
						INNER JOIN product p ON p.id = i.id
						LEFT OUTER JOIN deleted d ON d.id = i.id
					WHERE   i.Flag1 in (1, 2)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-07 : 15:01:17

Almost right

. the THEN should just be an expression rather then SET a value and you missed the closing END for the CASE (as well as the entire begin/end block. And I added the bit to only do the update if Flag1 was actually changed (or new).


BEGIN
       UPDATE  p SET 
              p.Flag1 = 
			CASE   
	        	WHEN i.Flag1 = 1 THEN 2
			WHEN i.Flag1 = 2 THEN 5
                     END
	FROM    inserted i
	INNER JOIN product p ON p.id = i.id
	LEFT OUTER JOIN deleted d ON d.id = i.id
	WHERE   i.Flag1 in (1, 2)   
       --Make sure Flag1 was either Updated (to a different value) or was an Insert
       and    i.Flag1 != isNull(d.Flag1,0)
END

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-07 : 15:14:35

Why are you using a trigger for this? It seems like a strange (incorrect) place to put your busines logic.

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-08 : 13:52:02

Thank you TG for your kind help and good explanation!

quote:
Originally posted by Lamprey

Why are you using a trigger for this? It seems like a strange (incorrect) place to put your busines logic.

Why would this be incorrect? My idea was to automatically indicate that a product is unavailable when a delivery has taken place. That might be a bad example but the general idea was to to be able to check between two or even more tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources