September 10, 2009 at 9:14 pm
I have a process that is already inserting data to a table, I have a trigger after insert that copies the information from the one table to an archive table, however, I need to delete the data that is currently in the table before the insert happens.
Coming from an oracle background, the before insert would work perfect, how can you do the instead of predicate to accomplish the same thing?
September 11, 2009 at 1:18 am
It could look like this:
CREATE TRIGGER TR_TEST
ON MyTable
INSTEAD OF INSERT
AS
BEGIN
TRUNCATE TABLE MyTable
INSERT INTO MyTable
SELECT *
FROM INSERTED
END
Hope this helps
Gianluca
-- Gianluca Sartori
September 11, 2009 at 2:14 am
Which table do you want to delete from... the original or the archive?
What data do you want to delete... all of it, or just rows matching the data being inserted?
September 11, 2009 at 8:13 am
You should be able to make the trigger:
A. Copy data matching your criteria(probably pulled from the inserted table to see what rows you're inserting) to your audit table.
B. Delete data from the table itself that matches criteria.
C. Insert the new data into your table (from the inserted table).
Some things to keep in mind:
You can use the virtual inserted table in MS SQL, which holds an exact copy of the row that will be inserted (or updated) to figure out criteria for deletion/audit.
Triggers only run once per batch, not once per row. You want to make sure you write it with this in mind (Don't use variables, etc.)
Cursors are bad.
If you need assistance actually writing the trigger, we'll need more specific information.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply