July 9, 2003 at 11:59 am
Using SQL 7, we need to implement a trigger to archive data. When a user changes a record, the trigger has to identify which column(s) were updated and then copy the original value of the column to an archive table before saving the newly entered value. We would appreciate any tips on how to cleanly implement this functionality. Thanks!
====================
"Awful tired now, Boss. Dog tired."
====================
"Awful tired now, Boss. Dog tired."
http://www.dwacon.com
July 9, 2003 at 12:09 pm
Two special tables are used in trigger statements: the deleted table and the inserted table.
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table.
An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
July 10, 2003 at 7:03 am
Here is a sample of a trigger used to insert records into another database when the rptflag field is updated. We select the necessary information from inserted and place it in the new database. In your case, you would select the information from deleted... Hope it helps.
CREATE trigger tu_PopulateInquiry on dbo.tblReportCPResults
for update as
--trigger to insert records in ServiceDB..Inquiry when CP+ Report is created
if @@rowcount = 0 return
if update(RptFlag)
begin
select i.TestID, i.CandidateFirstName, i.CandidateLastName, i.SSN, i.ReportDate,
tk.ASGCompanyID as ASGID, i.ExSelID, i.PCNNumber, i.FaxNumber, i.EmailAddress, i.AdminFirstName,
i.AdminLastName, i.OfficeName as CompanyDepartment, i.AddressLine1 as StreetAdderss1,
i.AddressLine2 as StreentAddress2, i.City, i.StateProvince, i.ZipPostal, i.Country,
'CP+' as Edition, i.ReportDeliveryMethod as Method, 0 as ScoringSystem,
i.UnscorableFlag as UnscorableReason, 0 as Rating,
' ' as AlternateRating
into #temp1
from deleted d join inserted i on i.testid = d.testid
--join tblCPScores s on d.TestID = s.TestID
join tk2000.dbo.Company tk on i.ExSelID = tk.CompanyId
where i.RptFlag = 1
--select * from inserted
--select * from #temp1
update #temp1
set #temp1.Rating = s.RawRating,
#temp1.AlternateRating = s.LetterRating,
#temp1.ScoringSystem = s.ScoringSystem
from #temp1 t join adhoc..tblCPScores s on t.TestID = s.TestID
where t.UnscorableReason is null
delete ServiceDB..tblInquiry
where TestID in (select testid from #temp1)
and Edition = 'CP+'
--print 'start'
insert ServiceDB..tblInquiry(TestId , CandidateFirstName , CandidateLastName , SocialSecurityNumber, DateProcessed , ASGId , ExselID , PCN , FaxNumber ,
EmailAddress , AdminFirstName , AdminLastName ,CompanyDepartment , StreetAddress1, StreetAddress2 , City , StateProvince, ZipPostal , Country, Edition , Method ,ScoringSystem, UnscorableReason, Rating ,AlternateRating )
select * from #temp1
--print @@rowcount
end
/*if @@error<>0
begin
raiserror ("Trigger failed to insert related records in Inquery table",16, 1)
rollback transaction
end*/
return
July 10, 2003 at 7:14 am
we've implemented this many times - in both financial and medical tracking apps - and one big tip i can give you is to go ahead and setup a full-blown audit table for each table you need an audit trail on - then every time an update or delete is performed on the base table, copy the whole row to the archived table...that way you will always be able to replicate data changes, no matter what - it makes your db bigger, but in many cases is required by law (medical) -- also add a 'date/time changed' field with some sort of data type that will store a date and time for you
another big advantage to this approach is that no matter how the app changes or the database table (adding cols), the trigger won't have to be modified
July 10, 2003 at 7:14 am
we've implemented this many times - in both financial and medical tracking apps - and one big tip i can give you is to go ahead and setup a full-blown audit table for each table you need an audit trail on - then every time an update or delete is performed on the base table, copy the whole row to the archived table...that way you will always be able to replicate data changes, no matter what - it makes your db bigger, but in many cases is required by law (medical) -- also add a 'date/time changed' field with some sort of data type that will store a date and time for you
another big advantage to this approach is that no matter how the app changes or the database table (adding cols), the trigger won't have to be modified
July 10, 2003 at 7:26 am
quote:
we've implemented this many times - in both financial and medical tracking apps - and one big tip i can give you is to go ahead and setup a full-blown audit table for each table you need an audit trail on - then every time an update or delete is performed on the base table, copy the whole row to the archived table...that way you will always be able to replicate data changes, no matter what - it makes your db bigger, but in many cases is required by law (medical) -- also add a 'date/time changed' field with some sort of data type that will store a date and time for you
agreed with that. I do this on two or three tables, which keep my financial transactions. They are a 1:1 copy of the original PLUS a datetime with getdate() as default AND a varchar field with S_USER_SNAME() as default. So I see easily who has done when what to my data. The storage overhead is at least in my case more than acceptable. And to be honest, I do not only have a trigger on UPDATE, but also on INSERT, which copies the newly inserted data to third table. Call my paranoid, but it saved my head one time.
Here's my example
CREATE TRIGGER updOrderzettel
ON tblOrderzettel
FOR update AS
--IF (COLUMNS_UPDATED() & 14) >0
BEGIN
INSERT INTO auditOrderzettel
(audit_log_type,
audit_OrderzettelID,
audit_UnternehmenID,
audit_LimitgeberID,
audit_LimitVorher,
audit_LimitNachher,
audit_Handelsdatum,
audit_HändlerID,
audit_Kontrolldatum,
audit_ZweiterHändlerID,
audit_WährungID,
audit_HandelsartID,
audit_Handelsvolumen,
audit_Stückzinstage,
audit_Stückzinsbetrag,
audit_Geschäftsvolumen,
audit_RisikoklasseID,
audit_Handelsbezeichnung,
audit_Handelskurs,
audit_Geschäftskurs,
audit_Buchkurs,
audit_Valuta,
audit_KontrahentID,
audit_KontrahentenMitarbeiterID,
audit_KontrolleBOID,
audit_KontrolleBODatum,
audit_HandelsbestätigungErstellt,
audit_HandelsbestätigungID,
audit_KPVTRansaktionErstellt,
audit_TransaktionsID,
audit_KapitalanlagenID,
audit_WKN,
audit_KANr,
audit_Fälligkeit,
audit_LagerstellenID,
audit_DepotartenID,
audit_GeschäftszweckID,
audit_ErstelltAm,
audit_ErstelltVonID,
audit_GeändertAm,
audit_GeändertVonID,
audit_ImportiertAm,
audit_ImportiertVonID,
audit_DeletedON,
audit_DeletedBy,
audit_PrintSuccess,
audit_RatingID,
audit_Spread,
audit_AccountingPrincipleID,
audit_Deleted)
SELECT 'OLD',
del.OrderzettelID,
del.UnternehmenID,
del.LimitgeberID,
del.LimitVorher,
del.LimitNachher,
del.Handelsdatum,
del.HändlerID,
del.Kontrolldatum,
del.ZweiterHändlerID,
del.WährungID,
del.HandelsartID,
del.Handelsvolumen,
del.Stückzinstage,
del.Stückzinsbetrag,
del.Geschäftsvolumen,
del.RisikoklasseID,
del.Handelsbezeichnung,
del.Handelskurs,
del.Geschäftskurs,
del.Buchkurs,
del.Valuta,
del.KontrahentID,
del.KontrahentenMitarbeiterID,
del.KontrolleBOID,
del.KontrolleBODatum,
del.HandelsbestätigungErstellt,
del.HandelsbestätigungID,
del.KPVTRansaktionErstellt,
del.TransaktionsID,
del.KapitalanlagenID,
del.WKN,
del."KA-Nr",
del.Fälligkeit,
del.LagerstellenID,
del.DepotartenID,
del.GeschäftszweckID,
del.ErstelltAm,
del.ErstelltVonID,
del.GeändertAm,
del.GeändertVonID,
del.ImportiertAm,
del.ImportiertVonID,
del.DeletedON,
del.DeletedBy,
del.PrintSuccess,
del.RatingID,
del.Spread,
del.AccountingPrincipleID,
del.Deleted
FROM deleted del
INSERT INTO auditOrderzettel
(audit_log_type,
audit_OrderzettelID,
audit_UnternehmenID,
audit_LimitgeberID,
audit_LimitVorher,
audit_LimitNachher,
audit_Handelsdatum,
audit_HändlerID,
audit_Kontrolldatum,
audit_ZweiterHändlerID,
audit_WährungID,
audit_HandelsartID,
audit_Handelsvolumen,
audit_Stückzinstage,
audit_Stückzinsbetrag,
audit_Geschäftsvolumen,
audit_RisikoklasseID,
audit_Handelsbezeichnung,
audit_Handelskurs,
audit_Geschäftskurs,
audit_Buchkurs,
audit_Valuta,
audit_KontrahentID,
audit_KontrahentenMitarbeiterID,
audit_KontrolleBOID,
audit_KontrolleBODatum,
audit_HandelsbestätigungErstellt,
audit_HandelsbestätigungID,
audit_KPVTRansaktionErstellt,
audit_TransaktionsID,
audit_KapitalanlagenID,
audit_WKN,
audit_KANr,
audit_Fälligkeit,
audit_LagerstellenID,
audit_DepotartenID,
audit_GeschäftszweckID,
audit_ErstelltAm,
audit_ErstelltVonID,
audit_GeändertAm,
audit_GeändertVonID,
audit_ImportiertAm,
audit_ImportiertVonID,
audit_DeletedON,
audit_DeletedBy,
audit_PrintSuccess,
audit_RatingID,
audit_Spread,
audit_AccountingPrincipleID,
audit_Deleted)
SELECT 'NEW',
ins.OrderzettelID,
ins.UnternehmenID,
ins.LimitgeberID,
ins.LimitVorher,
ins.LimitNachher,
ins.Handelsdatum,
ins.HändlerID,
ins.Kontrolldatum,
ins.ZweiterHändlerID,
ins.WährungID,
ins.HandelsartID,
ins.Handelsvolumen,
ins.Stückzinstage,
ins.Stückzinsbetrag,
ins.Geschäftsvolumen,
ins.RisikoklasseID,
ins.Handelsbezeichnung,
ins.Handelskurs,
ins.Geschäftskurs,
ins.Buchkurs,
ins.Valuta,
ins.KontrahentID,
ins.KontrahentenMitarbeiterID,
ins.KontrolleBOID,
ins.KontrolleBODatum,
ins.HandelsbestätigungErstellt,
ins.HandelsbestätigungID,
ins.KPVTRansaktionErstellt,
ins.TransaktionsID,
ins.KapitalanlagenID,
ins.WKN,
ins."KA-Nr",
ins.Fälligkeit,
ins.LagerstellenID,
ins.DepotartenID,
ins.GeschäftszweckID,
ins.ErstelltAm,
ins.ErstelltVonID,
ins.GeändertAm,
ins.GeändertVonID,
ins.ImportiertAm,
ins.ImportiertVonID,
ins.DeletedON,
ins.DeletedBy,
ins.PrintSuccess,
ins.RatingID,
ins.Spread,
ins.AccountingPrincipleID,
ins.Deleted
FROM inserted ins
END
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 10, 2003 at 8:13 am
Trued to write a trigger for this table basing it on the example you posted .Where am l going wrong ?
CREATE TABLE [TranTypes_New] (
[TranCode] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
[TranType] [char] (4) COLLATE Latin1_General_CI_AS NULL ,
[Descr] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[Dr_Code] [smallint] NULL ,
[Cr_Code] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TRIGGER updTranTypes
ON TranTypes_New
FOR update AS
--IF (COLUMNS_UPDATED() & 14) >0
BEGIN
INSERT INTO AuditTranTypes_New
(
audit_log_type,
audit_TranID,
audit_TranCode,
audit_TranType,
audit_Descr,
audit_Dr_Code,
audit_Cr_Code,
audit_Date,
audit_Deleted
)
SELECT 'OLD',
audit_TranID,
del_TranCode,
del_TranType,
del_Descr,
del_Dr_Code,
del_Cr_Code,
del_Date,
del_Deleted,
del.Deleted
FROM deleted del
INSERT INTO auditOrderzettel
(
audit_log_type,
audit_TranID,
audit_TranCode,
audit_TranType,
audit_Descr,
audit_Dr_Code,
audit_Cr_Code,
audit_Date,
audit_Deleted,
audit_Deleted
)
SELECT 'NEW',
ins.TranID,
ins_TranCode,
ins_TranType,
ins_Descr,
ins.del_Dr_Code,
del_Cr_Code,
del_Date,
del_Deleted,
del.Deleted
FROM inserted ins
END
July 10, 2003 at 8:16 am
Some thoughts and questions on this subject.
I've done something similar. I've also added a DELETE trigger, to mark in the audit log when and if the data got deleted, which has proven very useful on occasion.
There would seem to be two tactics to this: either write the data to the log as it is being changed, or as it is being entered. consider this set of events:
Insert 1, A
Update to 1, B
Update to 1, C
With "Audit on change", the audit table would only be added to on the updates, and only the data being overwritten would be added. At the end of the actions above, it'd have (1,A) and (1,B). With "Audit on create", the audit table would be added to for the insert and the updates, with the data being added or inserted. At the end of the series above, it'd have (1,A), (1,B), and (1,C).
The big advantage of "Audit on change" are that you store less data; the big advantage of "Audit on create" is that you'd have the history of all the data in one place.
So, which is better? What tactic would or does anyone use?
A last issue is data archive and purge. Since this data piles up quickly over time, presumably you archive and purge it out of the database regularly and consistantly, right?
Philip
July 10, 2003 at 8:23 am
quote:
Trued to write a trigger for this table basing it on the example you posted .Where am l going wrong ?audit_Deleted
deleted is a bit field in the original table which I haven't seen in your table definition, same for 'date' named field
Here's the DDL statement for the audit_table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[auditOrderzettel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[auditOrderzettel]
GO
CREATE TABLE [dbo].[auditOrderzettel] (
[audit_id] [int] IDENTITY (1, 1) NOT NULL ,
[audit_log_id] [uniqueidentifier] NULL ,
[audit_log_type] [char] (3) NOT NULL ,
[audit_OrderzettelID] [int] NULL ,
[audit_UnternehmenID] [int] NULL ,
[audit_LimitgeberID] [int] NULL ,
[audit_LimitVorher] [float] NULL ,
[audit_LimitNachher] [float] NULL ,
[audit_Handelsdatum] [datetime] NULL ,
[audit_HändlerID] [int] NULL ,
[audit_Kontrolldatum] [datetime] NULL ,
[audit_ZweiterHändlerID] [int] NULL ,
[audit_WährungID] [int] NULL ,
[audit_HandelsartID] [int] NULL ,
[audit_Handelsvolumen] [float] NULL ,
[audit_Stückzinstage] [float] NULL ,
[audit_Stückzinsbetrag] [float] NULL ,
[audit_Geschäftsvolumen] [float] NULL ,
[audit_RisikoklasseID] [int] NULL ,
[audit_Handelsbezeichnung] [varchar] (50) NULL ,
[audit_Handelskurs] [float] NULL ,
[audit_Geschäftskurs] [float] NULL ,
[audit_Buchkurs] [float] NULL ,
[audit_Valuta] [datetime] NULL ,
[audit_KontrahentID] [int] NULL ,
[audit_KontrahentenMitarbeiterID] [int] NULL ,
[audit_KontrolleBOID] [int] NULL ,
[audit_KontrolleBODatum] [datetime] NULL ,
[audit_HandelsbestätigungErstellt] [int] NULL ,
[audit_HandelsbestätigungID] [int] NULL ,
[audit_KPVTRansaktionErstellt] [int] NULL ,
[audit_TransaktionsID] [int] NULL ,
[audit_KapitalanlagenID] [int] NULL ,
[audit_WKN] [varchar] (50) NULL ,
[audit_KANr] [varchar] (50) NULL ,
[audit_Fälligkeit] [datetime] NULL ,
[audit_LagerstellenID] [int] NULL ,
[audit_DepotartenID] [int] NULL ,
[audit_GeschäftszweckID] [int] NULL ,
[audit_ErstelltAm] [datetime] NULL ,
[audit_ErstelltVonID] [int] NULL ,
[audit_GeändertAm] [datetime] NULL ,
[audit_GeändertVonID] [int] NULL ,
[audit_ImportiertAm] [datetime] NULL ,
[audit_ImportiertVonID] [int] NULL ,
[audit_DeletedON] [datetime] NULL ,
[audit_DeletedBy] [int] NULL ,
[audit_PrintSuccess] [int] NULL ,
[audit_RatingID] [int] NULL ,
[audit_Spread] [float] NULL ,
[audit_AccountingPrincipleID] [int] NULL ,
[audit_Deleted] [int] NULL ,
[audit_user] [nvarchar] (128) NOT NULL ,
[audit_changed] [datetime] NULL
) ON [PRIMARY]
GO
and for the original table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblOrderzettel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblOrderzettel]
GO
CREATE TABLE [dbo].[tblOrderzettel] (
[OrderzettelID] [int] IDENTITY (1, 1) NOT NULL ,
[UnternehmenID] [int] NULL ,
[LimitgeberID] [int] NULL ,
[LimitVorher] [float] NULL ,
[LimitNachher] [float] NULL ,
[Handelsdatum] [datetime] NULL ,
[HändlerID] [int] NULL ,
[Kontrolldatum] [datetime] NULL ,
[ZweiterHändlerID] [int] NULL ,
[WährungID] [int] NULL ,
[HandelsartID] [int] NULL ,
[Handelsvolumen] [float] NULL ,
[Stückzinstage] [float] NULL ,
[Stückzinsbetrag] [float] NULL ,
[Geschäftsvolumen] [float] NULL ,
[RisikoklasseID] [int] NULL ,
[Handelsbezeichnung] [varchar] (50) NULL ,
[Handelskurs] [float] NULL ,
[Geschäftskurs] [float] NULL ,
[Buchkurs] [float] NULL ,
[Valuta] [datetime] NULL ,
[KontrahentID] [int] NULL ,
[KontrahentenMitarbeiterID] [int] NULL ,
[KontrolleBOID] [int] NULL ,
[KontrolleBODatum] [datetime] NULL ,
[HandelsbestätigungErstellt] [int] NULL ,
[HandelsbestätigungID] [int] NULL ,
[KPVTRansaktionErstellt] [int] NULL ,
[TransaktionsID] [int] NULL ,
[KapitalanlagenID] [int] NULL ,
[WKN] [varchar] (50) NULL ,
[KA-Nr] [varchar] (50) NULL ,
[Fälligkeit] [datetime] NULL ,
[LagerstellenID] [int] NULL ,
[DepotartenID] [int] NULL ,
[GeschäftszweckID] [int] NULL ,
[ErstelltAm] [datetime] NULL ,
[ErstelltVonID] [int] NULL ,
[GeändertAm] [datetime] NULL ,
[GeändertVonID] [int] NULL ,
[ImportiertAm] [datetime] NULL ,
[ImportiertVonID] [int] NULL ,
[DeletedON] [datetime] NULL ,
[DeletedBy] [int] NULL ,
[PrintSuccess] [int] NULL ,
[RatingID] [int] NULL ,
[Spread] [float] NULL ,
[AccountingPrincipleID] [int] NULL ,
[Deleted] [int] NULL
) ON [PRIMARY]
GO
should work fine along with the trigger
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 10, 2003 at 8:26 am
i'm not sure you're going to save that much space, because with 'audit on change', your table needs to have twice as many columns than with 'audit on create':
for example you would need a column for 'column#1originalvalue' and 'column#1newvalue' for any udpates...
where if you used 'audit on create', you'd just need a copy of the data table with maybe an extra column for 'dml action type' to store the type of dml action (update, delete, insert) - or you could just use the timestamp to figure that out.
if you keep the table structure for the audit table very close to the same ddl as the original table, reporting is easier, updating is simpler, archiving is similar...
July 10, 2003 at 10:33 am
Ah, that's a slightly different approach than I was thinking. Let me spell out my idea bit more.
First off, as per a prior post the audit table has to have a datetime column (call it "Updated_At" for now, and stick DEFAULT CURRENT_TIMESTAMP on it); columns to track who did the update would also be good if you can get that information.
With this, you'd just store a single set of the columns, not a "before and after *as of right now* set... and now you have a historical log, ordered by date and time (down to the thousandth of a second), of what the data was and when it was. To track changes over time you would indeed have to retrieve a lot of rows and do a lot of row/column comparisons, but I'd think you'd have to do that no matter how you're storing the data when tracking down who changed what where and when.
Philip
July 28, 2003 at 12:52 pm
The solution I tried:
IF EXISTS
(Select 1 FROM Critical_Attributes
WHERE Table_Name = 'Network_Adapters'
AND Column_Name = 'IP_Subnet')
BEGIN
INSERT INTO Critical_History (Table_Name, Column_Name, Field_Data, Date_Archived)
SELECT 'Network_Adapters', 'IP_Subnet', d.IP_Subnet, GETDATE()
FROM #deleted d
INNER JOIN #inserted i
ON d.Network_Adapters_id=i.Network_Adapters_id
END
And the error message I receive:
[Microsoft][ODBC SQL Driver][SQL Server]Invalid object name '#deleted'
[Microsoft][ODBC SQL Driver][SQL Server]Invalid object name '#inserted'
Any clues on why this is not working? Thanks in advance!
====================
"Awful tired now, Boss. Dog tired."
====================
"Awful tired now, Boss. Dog tired."
http://www.dwacon.com
July 29, 2003 at 1:24 am
Hi
Try to remove the # before inserted and deleted tables.
/Fredrik
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply