May 30, 2003 at 10:40 am
I have set up an archive table that keeps track of all deleted records from its associated "master" table. I have the delete trigger working fine, where my question lies is in changes to the master tables structure. If I make a change, let's say I add a new column, is there any way to automatically update the associated archive table and add that column?
May 30, 2003 at 10:54 am
can you post some code so we can get an idea of the procedure?
May 30, 2003 at 11:00 am
So far all I've done is create a copy of the original table (tester) and name it tester_deleted. Then I have a delete trigger on the tester table that is "insert into tester_deleted select * from deleted"
What I'm looking to do is make sure if there is a structural change to tester (add, remove, or change a column) that the change is also made on tester_deleted.
May 30, 2003 at 11:06 am
does does the "select * from deleted" work as a stored procedure in SQL?
May 30, 2003 at 11:07 am
I'm not sure, right now it is in a trigger.
May 30, 2003 at 11:19 am
i want to create a procedure that shows deletions, just like this.
May 30, 2003 at 11:50 am
I tried the same code in a stored procedure and it didn't work. If you simply want to display deletions after the fact create a trigger that does a "select * from deleted". Or you can use an instead of trigger.
May 30, 2003 at 12:05 pm
how can you create a trigger?
May 30, 2003 at 12:09 pm
See the CREATE TRIGGER topic in Books Online or look at the articles on triggers on this site.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 30, 2003 at 12:17 pm
where do you make it? is it a stored procedure?
May 30, 2003 at 12:36 pm
going back to my topic, does anyone know how to do what I'm asking for?
May 30, 2003 at 2:41 pm
of course i believe in the 'records should never be deleted only have a status set theory' since one status field takes less space than a whole table. Plus there is always going to be someone who wants it back.
Then you can schedule archiving much later.
quote:
I have set up an archive table that keeps track of all deleted records from its associated "master" table. I have the delete trigger working fine, where my question lies is in changes to the master tables structure. If I make a change, let's say I add a new column, is there any way to automatically update the associated archive table and add that column?
June 2, 2003 at 1:31 am
Hi matt1,
quote:
where do you make it? is it a stored procedure?
in EM right click on the table where you want to create the trigger -> All tasks ->Manage triggers. It pops up a window which looks like the 'stored procedure definition window'.
Now you can create your trigger
CREATE TRIGGER insKapitalanlagenummern
ON dbo.tblKapitalanlagenummern
FOR insert AS
BEGIN
INSERT INTO backupKapitalanlagenummern
(backup_KapitalanlagenID,
backup_KaNr,
backup_WKN,
backup_Kapitalanlagenbezeichnung,
backup_RisikoklasseID,
backup_Emission,
backup_Fälligkeit,
backup_CiL,
backup_CiV,
backup_PbL,
backup_PbV,
backup_StrukturiertesProdukt,
backup_Derivate,
backup_RIC,
backup_RatingID,
backup_EmittentID,
backup_ErstelltAm,
backup_ErstelltVonID,
backup_GeändertAm,
backup_GeändertVonID,
backup_DeletedON,
backup_DeletedBy,
backup_Deleted,
backup_FRVFonds,
backup_isin_nr,
backup_in_index,
backup_is_sust)
SELECT
ins.KapitalanlagenID,
ins."Ka-Nr",
ins.WKN,
ins.Kapitalanlagenbezeichnung,
ins.RisikoklasseID,
ins.Emission,
ins.Fälligkeit,
ins.CiL,
ins.CiV,
ins.PbL,
ins.PbV,
ins.StrukturiertesProdukt,
ins.Derivate,
ins.RIC,
ins.RatingID,
ins.EmittentID,
ins.ErstelltAm,
ins.ErstelltVonID,
ins.GeändertAm,
ins.GeändertVonID,
ins.DeletedON,
ins.DeletedBy,
ins.Deleted,
ins.FRVFonds,
ins.isin_nr,
ins.in_index,
ins.is_sust
FROM inserted ins
END
Click OK to save.
Next time you want to access your trigger you must select it from the name combo at the top on the window.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 2, 2003 at 11:30 am
is some of that german?
June 3, 2003 at 12:22 am
Hi matt1,
quote:
is some of that german?
err, yes. The field names are a mixture of german and english. Sorry, if this has lead to confusion.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply