I still new in SQL Trigger using Informix database. I really need help from the expert to solve my problem.
How can I TRIGGER tempTable on new record inserted then insert the new record into actualTable? After that I want to TRIGGER actualTable on new record inserted and delete the same record on tempTable?
Here is my SQL TRIGGER:
CREATE TRIGGER t1
INSERT ON tempTable
REFERENCING NEW AS original
FOR EACH ROW WHEN (original.status = ‘Y’)
(INSERT INTO actualTable
(field1,field2,field3,status)VALUES(original.field1,original.field2,original.field3,original.status));
CREATE TRIGGER t2
INSERT ON actualTable
REFERENCING NEW AS original
FOR EACH ROW WHEN (original.status = ‘Y’)
(DELETE tempTable WHERE field1=original.field1);
Here is error found:
Error: Table or column matches object referenced in triggering statement. (State:S1000, Native Code: FFFFFD15)
