SQL Triggers - General help

~J~ · 26 Jan 2007 at 13:15

Anyone have a good example on how to use a trigger?

All I want is to update a field in a table (table a) depending on what value has been inserted in another a particular field in another table (table b).

Any pointers?

~J~ · 26 Jan 2007 at 23:06

Brilliant, thanks for the help folks.

~J~ · 22 Feb 2007 at 12:27

Post revival here!!

Managed to sort out my insert triggers no problems, but my update ones are proving more difficult.

Is there any way I can get the current value BEFORE the update so that I can use it to perform calculations before the new value is commited?

~J~ · 22 Feb 2007 at 12:57

Cheers, the book I've got here uses a "REFERENCES", but this doesn't seem to work under SQL2005, likewise SQL2000 has a BEFORE or AFTER clause, but 2005 assumes you're choosing a BEFORE unless you specifically say "AFTER".

Weird.

~J~ · 22 Feb 2007 at 14:00

OK well if anyone's interested, SQL2005 has a FANTASTIC feature called "INSTEAD OF" where you enter this after the creation of the trigger and put your code. Your code is then run INSTEAD OF what SQL server would normally do!

Brilliant!

Stupid example is:

Create Trigger NastyPasty ON Order.Items
INSTEAD OF
FOR INSERT
AS
BEGIN
DELETE * FROM Order.Items
END

~J~ · 23 Feb 2007 at 10:19

Yeah you're right, didn't realise it was in 2000 as well!!