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
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Would help if you post your existing trigger code, but assuming the schema of this table and EVENTS_DUPLCATED is exactly the same, here you go..
ALTER TRIGGER tr_your_trigger_name
AFTER INSERT

-- the rest of your trigger code here

INSERT INTO EVENTS_DUPLICATED
SELECT * FROM inserted
GO

Open in new window

If not the same, then you'll have to spell out the columns in both the INSERT INTO and SELECT clause.  This is best practice anyways.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
websssCEOAuthor Commented:
Thanks Jim

The table makes up most of the 100GB DB
Will you code copy newly inserted records only?
Jim HornMicrosoft SQL Server Data DudeCommented:
Yes.  With triggers there are two 'virutal' tables.
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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

websssCEOAuthor Commented:
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

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Means that you want to insert in the duplicated table the rows already modified and formatted. You can try to add the following code to the end of your trigger (don't forget to replace ID with the correct primarey key column):
INSERT INTO EVENTS_DUPLICATED
SELECT * 
FROM events
    INNER JOIN inserted ON events.ID = inserted.ID

Open in new window

websssCEOAuthor Commented:
So if i create a new trigger  "AFTER INSERT" that will fire after the initial FOR TRIGGER has processed?
Jim HornMicrosoft SQL Server Data DudeCommented:
<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.
websssCEOAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
I only need it to copy the Inserted row to the DUPLICATED table and not whole table rows
That's why it needs the JOIN with the inserted table. This guarantee that only the new row is copied.
websssCEOAuthor Commented:
Hi

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.

Open in new window

websssCEOAuthor Commented:
oh dont worry, turned off identity seed on the new table
Jim HornMicrosoft SQL Server Data DudeCommented:
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
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
oh dont worry, turned off identity seed on the new table
You need to if you want to duplicate the records :)
Andrei FomitchevCommented:
You can use Service Broker from Trigger.
It can capture changes even if Rollback was executed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.