Triggers upon table changes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Triggers upon table changes

Author

Topic

GrindCrusher
Starting Member

7 Posts

Posted - 2003-02-06 : 14:36:57

I havent worked with triggers as of yet and would like to try and implement one.
The table I want to monitor cahnges on has 20-30 fields. The table I want to write to only has 4 fields:

field name changed, original value of field, new value of field, time the change took place

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-06 : 15:19:11

Well................I guess you could write it, but it would slow you down since you would have to check each and every field, every time a row changed. Why not just write the entire row to a history table?

Do you still need the history table this way?

Anyone with any thoughts about this

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-06 : 15:23:50

Just another thought...what are you going to do with columns that have different datatypes? Would you convert everything to varchar(nnn)?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-06 : 15:32:06

OK, Here it is, but don't tell ANYONE where you got it (I'm assuming there is some crushing need, but I still dont reccommend it, this has to be a cpu/ IO pig):

Create table table1_h (Hist_Add_Dt datetime, Fieldname varchar(255), Value_Before varchar(255), Value_After varchar(255))
Go

CREATE TRIGGER Table1_UpdTr ON Table1
FOR UPDATE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

If (Select 1 From Inserted i, Deleted d Where i.key=d.key And i.col1 <> d.Col2)
BEGIN
INSERT INTO TABLE1_H Hist_Add_Dt, Fieldname, Value_Before, Value_After)
SELECT GetDate(), 'Col1' As FieldName, d.col1, i.col1
END
-- If Column Datatype is an Numeric
If (Select 1 From Inserted i, Deleted d Where i.key=d.key And i.col1 <> d.Col2)
BEGIN
INSERT INTO TABLE1_H Hist_Add_Dt, Fieldname, Value_Before, Value_After)
SELECT GetDate(), 'Col2' As FieldName, Convert(varchar(255),d.col1), Convert(varchar(255),i.col1)
END
-- If Column Datatype is a Date
If (Select 1 From Inserted i, Deleted d Where i.key=d.key And i.col1 <> d.Col2)
BEGIN
INSERT INTO TABLE1_H Hist_Add_Dt, Fieldname, Value_Before, Value_After)
SELECT GetDate(), 'Col3' As FieldName, Convert(varchar(255),d.col1,109)
, Convert(varchar(255),i.col1,109)
END
ect.......
END
GO

GrindCrusher
Starting Member

7 Posts

Posted - 2003-02-06 : 15:46:51

What im trying to due is write an event log that administrators will be able tolook for what field was changed. What The old value was , what the new value is, who made the change and when the change was made

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-06 : 15:59:42

Well, that will do it...but I'm assuming there is a front end to this (and a secure one too?)

Also I'm sassuming the front end will read a data row before it updates it. Maybe the fron end could capture the data "on change" event and either write it to a text file or insert a row to history..

what'dya think?

Brett

8-)

sherrer

64 Posts

Posted - 2003-02-06 : 16:48:05

I would go with what Brett is saying and create a history table. It is much more efficient than hashing all the fields in the manor you are suggesting. Here is a trigger from our database as an example.

CREATE TRIGGER [trg_updAccout] ON dbo.Account FOR UPDATE AS
BEGIN
IF @@rowcount = 0 RETURN
-- Audit OLD record.
INSERT INTO auditAccount
SELECT GETDATE(), 'UPD', deleted.*, (select nt_username from master..sysprocesses where spid = @@spid)
FROM deleted
END

The audit table has 3 extra fields that the production table doesn't. It has a DateTimeChanged, ChangeType, (Data from the table), and we stored the nt username as well. You could plug in the database user or the host machine as well if you would like, they are all easy to get from the database.

Then you could write a front end to do whatever it is you need done. You would have to hash the data at the time you need to look at it, by field, user, ect., but how often do you need to see any particular set of data?

Hope this helps.

GrindCrusher
Starting Member

7 Posts

Posted - 2003-02-06 : 18:09:13

If I make a change to record # 152 look through each field of #152 and write those changes to the event history not look through each and every record to see which fields are changed. So I would need to throw in the record number as an input parameter.
An example I came across is this:

CREATE TRIGGER yourTriggerNameHere
ON yourTableNameHere
FOR UPDATE
AS
declare @intColumnCount int,
@intColumn int

Select @intColumnCount = Count(Ordinal_position)
From Information_Schema.Columns
Where Table_name = "yourTableNameHere"

Select Columns_Updated()
Select @intColumn = 1

While @intColum <= & @intColumnCount
if Columns_Updated()& @intColumn = @intColumn
Begin
' Write to my table the changed fields info
End

GrindCrusher
Starting Member

7 Posts

Posted - 2003-02-11 : 14:09:16

Actually Im looking for pulling information from the inserted and deleted tables ....Specifically field name and the changed values

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 14:42:03

We implement something very similiar to this. It's a heavily hit table and performance doesn't seem to suffer. The trigger looks like so. I only pulled a few columns for you to demonstrate several things (straight inserts, joins, converting to user readable data and null comparisons). The biggest caveat being null comparions. If either of the columns in the where clause has a null value then you will not get a join. Simply wrap the ISNULL function around both sides of the join.

CREATE TRIGGER t_upd_Employee_002 ON dbo.Employee
FOR UPDATE
AS

--Exclude 'system' fields!!!
IF UPDATE(EmployeeSSN)
INSERT INTO <db>.dbo.EmployeeTransactiON
(EmployeeKey, DateChanged, FieldName, PreviousValue, NewValue)
SELECT ins.InternalKey, GETDATE(), 'Employee SSN', del.EmployeeSSN, ins.EmployeeSSN
FROM inserted AS ins
INNER JOIN deleted AS del
ON ins.InternalKey = del.InternalKey
WHERE ins.EmployeeSSN <> del.EmployeeSSN

IF UPDATE(FirstName)
INSERT INTO <db>.dbo.EmployeeTransactiON
(EmployeeKey, DateChanged, FieldName, PreviousValue, NewValue)
SELECT ins.InternalKey, GETDATE(), 'First Name', del.FirstName, ins.FirstName
FROM inserted AS ins
INNER JOIN deleted AS del
ON ins.InternalKey = del.InternalKey
WHERE ins.FirstName <> del.FirstName

IF UPDATE(StateKey)
INSERT INTO <db>.dbo.EmployeeTransactiON
(EmployeeKey, DateChanged, FieldName, PreviousValue, NewValue)
SELECT ins.InternalKey, GETDATE(), 'State', delS.Description, insS.Description
FROM inserted AS ins
INNER JOIN deleted AS del
ON ins.InternalKey = del.InternalKey
LEFT JOIN <db>.dbo.State AS insS
ON ins.StateKey = insS.InternalKey
LEFT JOIN <db>.dbo.State AS delS
ON del.StateKey = delS.InternalKey
WHERE ins.StateKey <> del.StateKey

IF UPDATE(BirthDate)
INSERT INTO <db>.dbo.EmployeeTransactiON
(EmployeeKey, DateChanged, FieldName, PreviousValue, NewValue)
SELECT ins.InternalKey, GETDATE(), 'Birth Date', Convert(nvarchar(10), del.BirthDate, 101), Convert(nvarchar(10), ins.BirthDate, 101)
FROM inserted AS ins
INNER JOIN deleted AS del
ON ins.InternalKey = del.InternalKey
WHERE ISNULL(ins.BirthDate, '') <> ISNULL(del.BirthDate, '')

GrindCrusher
Starting Member

7 Posts

Posted - 2003-02-12 : 17:39:52

Thnx for the info guyz..............So far I went this idea, until I can find something more dynamic for 45 + columns

IF UPDATE(serial_number)
Begin
insert into ref_server_event_log (asset_id, event_date,event_login_id,event_change_from, event_change_to,event_field,event_action)

Select i.asset_id,GetDate(),i.updated_by,d.serial_number,i.serial_number,
'Serial Number','Updated'

FROM deleted d
INNER JOIN inserted i ON d.asset_id = i.asset_id
WHERE ISNULL(d.serial_number, '') <> ISNULL(i.serial_number, '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources