Can I create a trigger to add another record based on the inserted record in the same table?

[Mohd Shaiza Ibrahim <mohdshaiza@xxxxxxxxx>]

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