Обсуждение: Can I create a trigger to add another record based on the inserted record in the same table?

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

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.

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.