SQL Triggers - General help

~J~

~J~

~J~

Soldato
Joined
20 Oct 2003
Posts
7,558
Location
London
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?
 
Which DBMS?

It should just be a case of defining a trigger on table b for the INSERT action which performs the necessary update. For DB2 you'd be looking at something like...

Code: 
create trigger trigger_name 
after insert on table_b 
referencing new as newrow
for each row mode db2sql
update table_a set column_name = newrow.column_name2
 
and for SQL Server it would be something along the lines of

create trigger Trig_test ON
TableB
FOR INSERT
AS
UPDATE TableA
SET TableA..Field = inserted.field
FROM inserted
WHERE inserted.row_id = TableA.row_id
 
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?
 
I'm fairly sure that there is a way to access to row's value before the update occurs in the on-update trigger.

I'll look it up in my SQL Server 2K book when I get home for you unless someone's answered you already.

Paul

edit: or just use the before-update trigger?
 
Last edited:
I think there is a temporary table called inserted and another called deleted available inside the trigger, deleted would hold the original value but be careful if it's not a per record trigger.
 
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.
 
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

:D
 
You must log in or register to reply here.
Back
Top Bottom