CODEWITHSUNDEEP
oracle-database
melat
melat

Reputation: 7

How to create a trigger that moves data to another table when a table is truncated

I created the trigger but it doesn't move data to the other table

It creates the trigger successfully. It also truncates the table but the data is not moved.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE TRUNCATE ON SCHEMA
BEGIN
IF (ora_sysevent='TRUNCATE' and ora_dict_obj_name='testtab') THEN
INSERT INTO testtab2
SELECT name, id FROM testtab;
END IF;
END test_trigger;

Upvotes: 0

Views: 81

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

From the other references your source table does not have a quoted identifier, so it will be stored in uppercase in the data dictionary etc. Read more in the docs.That means you need the comparison to be uppercase too:

and ora_dict_obj_name='TESTTAB'

Not related, but it is good practice for your insert statement to list the target column names:

INSERT INTO testtab2 (name, id)
SELECT name, id FROM testtab;

Upvotes: 1

Related Questions