Trouble shooting a Update, Insert TRIGGER...what's wrong with this syntax ?

Thank you MM, that did the trick...but could you please expand what you meant with INSERTED virtual table function ?

Doesnt my trigger now only update future rows in Table B, only if the the payor type in Table A is 'C' ?

Could you please show me the possible syntax ?

Thank you

Well your code does not consider which rows in A have been updated. It will update all rows in B regardless.

I don't understand your bit about "future rows"...

I think you should read about inserted and deleted virtual tables. They are critical to writing triggers that actually work.

http://msdn.microsoft.com/en-us/library/ms191300%28v=sql.105%29.aspx

It should look something like this:

CREATE TRIGGER [dbo].[Joe_trigger] ON [dbo].[TableA]

AFTER INSERT, UPDATE

AS

BEGIN;

UPDATE B

SET LAST_MODIFIED = I.LAST_MODIFIED

FROM INSERTED AS I

INNER JOIN TABLEB AS B

ON I.MATTER_UNO = B.MATTER_UNO

WHERE I.payor_Type = 'C';

END;

If you're using SQL 2008 (as per forum posted in), consider switching the Update From into a Merge statement.

That would look like:

CREATE TRIGGER [dbo].[Joe_trigger] ON [dbo].[TableA]

AFTER INSERT, UPDATE

AS

BEGIN;

MERGE INTO TableB AS Tgt

USING

(SELECT LAST_MODIFIED,

MATTER_UNO

FROM INSERTED

WHERE payor_Type = 'C') AS Src

ON Tgt.MATTER_UNO = Src.MATTER_UNO

WHEN MATCHED

THEN UPDATE

SET LAST_MODIFIED = Src.LAST_MODIFIED;

END;

Merge has some safeguards built into that prevent things like updating the same row twice. Has the added advantage of being ISO-compliant, if that matters to you (does to some, doesn't to others), while Update From is T-SQL proprietary.

Thanks guys...I like that merge function, and I will read up a bit more.

Overall, I only want this trigger to fire when a row is added or a row is changed going forward...this is what I meant with future rows...I dont want it to fire for what is in there now...does the syntax provided by GSquared do that ?

Hope Im being clear enough.

Remember that triggers only fire when something happens. Both versions of the code G2 provided will do what you are asking. The best way for you to determine that is to run this in your test/dev environment and check it out. Above all else make sure you understand the code. You are the one that has to debug it when production comes crashing down. Most people won't be too keen on "I don't really understand the code, I got from somebody on the internet". 😀

Triggers only fire when data is modified. In this case, the trigger calls for firing on Update and Insert statements. If you delete a row (or more than one), the trigger will ignore that, and the Last Update data in TableB will be unchanged.

The use of the "inserted" table in the trigger ensures that it only does anything with the rows in TableA that have been modified by the command that is causing the trigger to fire.

For example, if you update Row 1, but not rows 2 through 1000, the inserted table will only contain data for Row 1. Since you join from that to TableB, the only row(s) affected in TableB will be those that match something in "inserted"; in this case, Row 1.

There is also a "deleted" table that triggers can access. Insert statements only generate rows in "inserted". Updates generate rows in "deleted" (data before the update) and in "inserted" (new data). Delete statements only generate rows in "deleted".

Read up them here: http://technet.microsoft.com/en-us/library/ms191300(v=sql.105).aspx

(You can adjust what version of SQL Server you are reading about with a drop-down at the top of that page.)

Does that help?

that helps, thank you very much everybody !