duplicating table
Hi
I have a table called EVENTS
This table has a trigger that runs on data inserted
The trigger alters some of the data being inserted (formatting etc)
At the end of this trigger, I would like to duplicate the row that was just entered and put into another table called
EVENTS_DUPLICATED
It will be exactly the same data,
What is the quickest and easiest way to duplicate the row just inserted and put into the DUPLICATED table
I cannot do it before the trigger completes as some of the data in the row changes, so it needs to be the last thing the existing trigger does
I have a table called EVENTS
This table has a trigger that runs on data inserted
The trigger alters some of the data being inserted (formatting etc)
At the end of this trigger, I would like to duplicate the row that was just entered and put into another table called
EVENTS_DUPLICATED
It will be exactly the same data,
What is the quickest and easiest way to duplicate the row just inserted and put into the DUPLICATED table
I cannot do it before the trigger completes as some of the data in the row changes, so it needs to be the last thing the existing trigger does
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes. With triggers there are two 'virutal' tables.
If you need to get real creative and have this action occur not in a trigger but on the query itself, there's the OUTPUT clause.
Deleted - All rows deleted, and the 'old' row just updated, in any query.
Inserted - All rows inserted, and the 'new row just updated, in any query.
So if your query inserts five rows, deleted will be empty, and inserted will contain five rows, and not all rows in the table.If you need to get real creative and have this action occur not in a trigger but on the query itself, there's the OUTPUT clause.
ASKER
One thing i noticed is your code on trigger said AFTER insert, where as the trigger says FOR INSERT
ALTER TRIGGER [dbo].[Events_I] ON [dbo].[Events]
FOR INSERT
FOR INSERT means the initial insert can be cancelled, AFTER INSERT means it is after the insert and cannot be answered. This can come into play if there are multiple triggers on a table.
Your call.
Your call.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So if i create a new trigger "AFTER INSERT" that will fire after the initial FOR TRIGGER has processed?
<Correction to the above comment> There isn't any difference between FOR and AFTER triggers. From what I'm reading FOR was the initial syntax, but AFTER was added to make it ANSI compliant.
The MSDN page on CREATE TRIGGER doesn't make that exact point
FOR | AFTER
AFTER specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
The MSDN page on CREATE TRIGGER doesn't make that exact point
FOR | AFTER
AFTER specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
ASKER
Thanks vitor
Will your code copy all rows?
I only need it to copy the Inserted row to the DUPLICATED table and not whole table rows
Will your code copy all rows?
I only need it to copy the Inserted row to the DUPLICATED table and not whole table rows
I only need it to copy the Inserted row to the DUPLICATED table and not whole table rowsThat's why it needs the JOIN with the inserted table. This guarantee that only the new row is copied.
ASKER
Hi
I got the following error with your code JIM (when executing trigger)
I got the following error with your code JIM (when executing trigger)
Msg 8101, Level 16, State 1, Procedure Events_I, Line 148
An explicit value for the identity column in table 'Events_Duplicated' can only be specified when a column list is used and IDENTITY_INSERT is ON.ASKER
oh dont worry, turned off identity seed on the new table
For starters, it helps to post all of your code so we can see all of what's going on.
>An explicit value for the identity column in table 'Events_Duplicated' ...
Ok. There's an identity column in EVENTS_DUPLCATED, so you can't intentionally insert a row into it.
Two options
>An explicit value for the identity column in table 'Events_Duplicated' ...
Ok. There's an identity column in EVENTS_DUPLCATED, so you can't intentionally insert a row into it.
Two options
Lose the identity of that column. If this is a logging table, then you don't need it anyways, as the value is generated from EVENTS. Guessing this is the case here.
Do not insert that column, but spell out all the rest in the INSERT INTO and SELECT clause.
oh dont worry, turned off identity seed on the new tableYou need to if you want to duplicate the records :)
You can use Service Broker from Trigger.
It can capture changes even if Rollback was executed.
It can capture changes even if Rollback was executed.
ASKER
The table makes up most of the 100GB DB
Will you code copy newly inserted records only?