Hi,
Can you guys please help me? My question sounds like this.
When I insert a new record in a table, can I create a trigger to add
another record based on the inserted record in the same table?
For example,
INSERT INTO employee (emp_id, emp_name) VALUES (0001, 'Jack');
The result:
Select * from employee;
emp_id | emp_name
0001 | Jack
0002 | Bob
I've tried running the statement below but it doesn't work. Infinite
loop i'm guessing.
--CREATE FUNCTION AS .. RETURNS TRIGGER
CREATE OR REPLACE FUNCTION add_employee_trg()
RETURNS trigger AS
$BODY$ DECLARE
BEGIN
--DELETE STATEMENT
IF tg_op = 'DELETE' THEN
INSERT INTO employee_bak(emp_id, emp_name, emp_operation)
VALUES (old.emp_id, old.emp_name, tg_op);
RETURN old;
END IF;
--INSERT STATEMENT
IF tg_op = 'INSERT' THEN
INSERT INTO employee_bak(emp_id, emp_name, emp_operation)
VALUES (new.emp_id, new.emp_name, tg_op);
RETURN new;
END IF;
--UPDATE STATEMENT
IF tg_op = 'UPDATE' THEN
INSERT INTO employee_bak(emp_id, emp_name, emp_operation)
VALUES (old.emp_id, new.emp_name, tg_op);
RETURN new;
END IF;
END
; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION add_employee_trg()
OWNER TO postgres;
Any help or guide would really be appreciated.
Thanks.
Shai
--
Mohd Shaiza Ibrahim