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

On Jul 18, 2012, at 8:37, David Johnston  wrote:

> On Jul 17, 2012, at 20:59, Mohd Shaiza Ibrahim  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 inserts to 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.