Trigger on insert

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Trigger on insert

Trigger on insert

Rate Topic

Display Mode

Topic Options

Author

Message

rajasekhar.cn

Posted Monday, January 28, 2013 1:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 4:35 AM
Points: 3, Visits: 6

Hi ,

I have a requirement where i have a table (EMP) with fields id, name, status

What i want is after each insert write the data to another table(EMP_BACKUP) and update the status of EMP table to 1.

Thanks
Raj

Post #1412244

anthony.green

Posted Monday, January 28, 2013 2:00 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

How often is the table going to be inserted into?

But a simple insert trigger would do the job, if the inserts are very few and far between.

CREATE TRIGGER ..... ON .....
FOR INSERT
AS
INSERT INTO .....
SELECT .....
FROM
INSERTED

UPDATE ....
SET ... = ....
FROM ...
INNER JOIN 
INSERTED
ON ... = ...

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412250

rajasekhar.cn

Posted Monday, January 28, 2013 2:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 4:35 AM
Points: 3, Visits: 6

Hi,

I have written a trigger

------------------------------------------------------------------
create or replace
trigger fetched_records_trigger
after insert on EMP for each row

DECLARE
--v_username varchar2(10);
BEGIN
insert into EMP_BACKUP values (:new.id , :new.COUNT_PER_FETCH, :new.SUCCESS_COUNT, :new.FAILURE_COUNT, :new.FAILURE_DES, :new.STATUS);

update EMP set status=1;

END;

Am able to insert the data into EMP_BACKUP table but cannot update the status column in EMP table to 1
Am getting the below errror
-------------------------------
SQL Error: ORA-04091: table KCBUSER.FETCHED_RECORDS is mutating, trigger/function may not see it
ORA-06512: at "KCBUSER.FETCHED_RECORDS_TRIGGER", line 6
ORA-04088: error during execution of trigger 'KCBUSER.FETCHED_RECORDS_TRIGGER'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

Post #1412272

anthony.green

Posted Monday, January 28, 2013 2:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Ah, an Oracle trigger, you would of been best posting that in the "Working with Oracle" section instead as this is primarily a Microsoft SQL Server forum.

Someone with Oracle experience may find this post and help you out, but I don't know Oracle, so I would recommend reading the Oracle Documentation on creating triggers.

Post #1412275

« Prev Topic | Next Topic »

Permissions