Trigger - create after record changed

Transact-SQL
1

Need some help with the following trigger.

ALTER TRIGGER [dbo].[after_update] ON [dbo].[TBL1]
AFTER UPDATE
AS
If UPDATE(CHANGED)
BEGIN
UPDATE dbo.[TBL2]
SET dbo.[TBL2].CHANGED = getdate()
from TBL1
WHERE TBL1.ORDER = TBL2.ORDER

END

This trigger should only update TBL2 when CHANGED value is 4 , if not no update should take place. how do I change the code for this?

Thanks

2

Hi,

Try this

ALTER TRIGGER [dbo].[after_update] ON [dbo].[TBL1]
AFTER UPDATE
AS
If UPDATE(CHANGED)
BEGIN
UPDATE T2
SET dbo.[TBL2].CHANGED = getdate()
from INSERTED T1
INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
WHERE T1.Changed = 4
END

3

Thank you this works for me so. Now I would need to update couple of fields depending on Changed if Changed = 4 need to update dbo.[TBL2].CHANGED4
if changed = 5 then dbo.[TBL2].CHANGED5
if changed = 8 then dbo.[TBL2].CHANGED8

ALTER TRIGGER [dbo].[after_update] ON [dbo].[TBL1]
AFTER UPDATE
AS
If UPDATE(CHANGED)
BEGIN
UPDATE T2
SET dbo.[TBL2].CHANGED4 = getdate()
from INSERTED T1
INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
WHERE T1.Changed = 4

SET dbo.[TBL2].CHANGED5 = getdate()
from INSERTED T1
INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
WHERE T1.Changed = 5

SET dbo.[TBL2].CHANGED8 = getdate()
from INSERTED T1
INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
WHERE T1.Changed = 8

END

4

Maybe:

ALTER TRIGGER [dbo].[after_update]
ON [dbo].[SorMaster]
AFTER UPDATE
AS
BEGIN;
	SET NOCOUNT ON;

	UPDATE M
        SET OrderStatus4 = CURRENT_TIMESTAMP
        FROM dbo.[CusSorMaster+] M
                JOIN inserted I
			ON M.SalesOrder = I.SalesOrder
                JOIN deleted D
			ON M.SalesOrder = D.SalesOrder
	WHERE I.OrderStatus = '4'
		AND D.OrderStatus <> '4';
END;
GO
5

Try this

ALTER TRIGGER [dbo].[after_update] ON [dbo].[TBL1]
AFTER UPDATE
AS
If UPDATE(CHANGED)
BEGIN

IF EXISTS(Select 1 from inserted i where i.changed = 4)
Begin
	UPDATE T2
	SET dbo.[TBL2].CHANGED4 = getdate()
	from INSERTED T1
	INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
	WHERE T1.Changed = 4
End

IF EXISTS(Select 1 from inserted i where i.changed = 5)
BEGIN
	UPDATE T2
	SET dbo.[TBL2].CHANGED5 = getdate()
	from INSERTED T1
	INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
	WHERE T1.Changed = 5
END

IF EXISTS(Select 1 from inserted i where i.changed = 8)
BEGIN
	UPDATE T2
	SET dbo.[TBL2].CHANGED8 = getdate()
	from INSERTED T1
	INNER JOIN dbo.[TBL2] T2 ON T1.ORDER = T2.ORDER
	WHERE T1.Changed = 8
END

END

6

What have you done so far[quote="Patyk, post:1, topic:9891"]
If UPDATE(CHANGED)
[/quote]

Note that this does not check if the value in [CHANGED] has actually changed, ONLY that that column is included in the UPDATE statement (ditto for INSERT)

Seems unnecesary to me - the UPDATE will update zero rows if there are no matches.

This will update the row even if the column has not changed. (That said, SQL will usually optimise that out, but I still prefer to check for it [as @Ifor's code did] )

I wouldn't make three separate updates statements without a TRANSACTION to ensure they either all complete, or none of them do; and I would want to avoid multiple updates it at all possible which would be more efficient and ATOMic.

My solution would be to use a single UPDATE something like this:

ALTER TRIGGER [dbo].[after_update]
ON [dbo].[TBL1]
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON
	UPDATE T2
	SET	  CHANGED4 = CASE WHEN I.changed = 4 THEN GetDate() ELSE T2.CHANGED4 END
		, CHANGED5 = CASE WHEN I.changed = 5 THEN GetDate() ELSE T2.CHANGED5 END
		, CHANGED8 = CASE WHEN I.changed = 8 THEN GetDate() ELSE T2.CHANGED8 END
	FROM	dbo.[TBL2] AS T2
			JOIN inserted AS I
				 ON I.ORDER = T2.ORDER
			JOIN deleted AS D
				 ON D.ORDER = T2.ORDER
				AND D.Changed <> I.Changed -- Only if [Changed] has been modified - Check for NULLs too??
	WHERE I.OrderStatus IN (4, 5, 8)
END
GO
1 Like