Trigger help

[manj]

Can anybody tell me how to create a trigger in Oracle.

This is the scenario. Insert a row into table A, all but two columns are inserted. The other remaining columns are then inserted via a trigger on the same row.

 

[MikeJones]

CREATE TRIGGER trigger_name on table_name before insert for each row
begin

Code here

end;

 

[manj]

Cheers Mike,

In the 'code here' section how can I reference the row that has just been inserted, and hence insert the additional columns on that specific row?

 

[MikeJones]

You can use ld.column_name and :new.column_name to refence the data as it comes in and as it comes out. I think... Actually I'm not sure, maybe you just use the :new in after insert, I cn't remember. Have a go and if it errors then get rid of the one it doesn't like.

I Think you could do the following...

:new.column_1 := 'Some predfined text';

One thing to mention is that I hope you don't plan on inserting lots of rows into this table. Triggers are very cumbersome on insert/update/delete statements...

HTH,

Mike.

 

[stevecal]

They certainly are cumbersome. The trick is to have all your triggers firing packaged stored procedures...

 

[MikeJones]

Even so, Triggers may still be too cumbersome. We've tried to use them to audit a highly volatile table and the performance nose-dived, we had to abandon the idea we couldn't get the triggers to perform quick enough, the table had around 1,000,000 inserts/Updates/Deletes per day.