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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
