Thread: Can I create a trigger to add another record based on the inserted record in the same table?
Can I create a trigger to add another record based on the inserted record in the same table?
From
Mohd Shaiza Ibrahim
Date:
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
Re: Can I create a trigger to add another record based on the inserted record in the same table?
From
David Johnston
Date:
On Jul 17, 2012, at 20:59, Mohd Shaiza Ibrahim <mohdshaiza@gmail.com> wrote: > 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. > > Infinite loop is correct. You need to fix your logic to solve that problem or consider a new design. Maybe restrict insertsto the table to a security definer function and put you dual insert logic into it. David J.
Re: Can I create a trigger to add another record based on the inserted record in the same table?
From
David Johnston
Date:
On Jul 18, 2012, at 8:37, David Johnston <polobo@yahoo.com> wrote: > On Jul 17, 2012, at 20:59, Mohd Shaiza Ibrahim <mohdshaiza@gmail.com> wrote: > >> 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. >> >> > > Infinite loop is correct. You need to fix your logic to solve that problem or consider a new design. Maybe restrict insertsto the table to a security definer function and put you dual insert logic into it. > > It may not be infinite trigger but you do not show the CREATE TRIGGER statement you are using so it is impossible to know. You mention same table but it appears you are trying to do audit logging which uses different tables. You also do not say what you mean by "it doesn't work". David J.