| Trigger to audit table [message #372309] |
Thu, 01 February 2001 12:22  |
ash
Messages: 43
Registered: February 2001
|
Member |
|
|
|
I want to create a trigger before and after update of table A. Before an update is done on a certain field in table A, I want the entire row to be inserted into a new table B. Similarly, after the update is done on table A in any column, I want the entire updated row to be inserted into table B. How can i do this?? Help!!
|
|
|
|
| Re: Trigger to audit table [message #372310 is a reply to message #372309] |
Thu, 01 February 2001 13:35  |
nobody
Messages: 5
Registered: February 2001
|
Junior Member |
|
|
you should only need an after insert or update trigger. the before values will always be equal to the after values of the last dml statement.
CREATE OR REPLACE TRIGGER MY_TRG
AFTER INSERT OR UPDATE ON MyTableName FOR EACH ROW
DECLARE
BEGIN
INSERT INTO TABLE_B (
COL1
,COL2
,COL3
)
VALUES(
:NEW.COL1
,:NEW.COL2
,:NEW.COL3
);
END MY_TRG;
/
|
|
|
|
| Re: Trigger to audit table [message #372311 is a reply to message #372309] |
Thu, 01 February 2001 13:41 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
create or replace trigger trigger_name
before update on tableA
for each row
begin
insert into tableB values(:old.colname1,:old.colname2);
end;
create or replace trigger trigger_name1
after update on tableA
for each row
begin
insert into tableB values(:new.colname1,:new.colname2);
end;
|
|
|
|
| Re: Trigger to audit table [message #372312 is a reply to message #372309] |
Thu, 01 February 2001 14:40 |
ash
Messages: 43
Registered: February 2001
|
Member |
|
|
|
I tried the same code, but I am getting an error that trigger_name invalid and failed re-validation. I am just trying on a single column and the table B has the same coulmns as table A. I am trying to get the trigger fired, so that the old data goes and sits in the new table.
|
|
|
|
|
|
| Re: Trigger to audit table [message #372314 is a reply to message #372310] |
Thu, 01 February 2001 15:11 |
ash
Messages: 43
Registered: February 2001
|
Member |
|
|
I am getting a compilation error when I create the trigger. It is asking for ; before END. Secondly I tried to update the table, then I got this error:
trigger_name is invalid and failed re-validation.
|
|
|
|
|
|
| Re: Trigger to audit table [message #372326 is a reply to message #372310] |
Fri, 02 February 2001 12:08 |
ash
Messages: 43
Registered: February 2001
|
Member |
|
|
create trigger test
before update of type
on ecsidb.cs_servers_save
for each row
begin
insert into ecsidb.cs_servers_audit_save(type)
values(:old.type)
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
