Requires
Helper function and
monitor table
This is a sample code of creating a trigger on the demo database
pubs which is included with MS SQLServer.
To customize it to your database, you need to change the various entities.
The legends used in this example are:
database, which in this example is
pubs
The statement, use
pubs, however should be removed.
It is written to illustrate the database which is used.
Creation of a
trigger requires the first statement to be CREATE
trigger, so connect to the right
database and comment out this line.
Comment by preceeding --
trigger, which in this example is
tr_storm
A trigger name must be unique to a database, so you may use your own mnemonics.
Could be tr_ followed by the tablename.
table, which in this example is
pub_info
Replace
pub_info to the tablename on which you would establish monitoring.
field, which in this example is
pr_info
Replace
pr_info to the fieldname you want to monitor.
Multiple fields can be monitored, in which case you can repeat the block from
-- ******* Start field block ************
to
-- ******* End field block, repeat for each field in table ************
keyfield, which in this example is
pub_id
This is the name of the field which identifies each record.
In the pubs example it is pub_id, which is a unique key on the pub_info table.
Notice it is a Varchar, and if you use some kind of integer/identity you need to change the definitions of @cpub_id.
Warning!
The line and space formatting may be corrupted due to a bug in Internet Explorer.
Copy paste may not work as expected.
The code to create the trigger copy/pasting in Query Analyzer ( or similar tool ):
-- This is an example of a trigger on the pub_info table in the pubs database checking the pr_info field logging the pub_id key value
use pubs
CREATE TRIGGER tr_storm ON pub_info
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
DECLARE @cpr_info INT, @cpub_id VARCHAR (20) -- pub_id is the key/identifier field
DECLARE inserted_cursor CURSOR LOCAL FOR
SELECT I.pub_id,dbo.CrC32(P.pr_info)
FROM INSERTED I
INNER JOIN pub_info P
ON I.pub_id=P.pub_id
OPEN inserted_cursor
FETCH NEXT FROM inserted_cursor INTO @cpub_id,@cpr_info
WHILE @@FETCH_STATUS = 0
BEGIN
-- ******* Start field block ************
IF NOT Exists(SELECT id FROM StormMonitor WHERE Tablename = 'pub_info' AND ColoumName = 'pr_info' AND KeyField = CAST(@cpub_id AS Varchar) ) BEGIN
insert into stormmonitor VALUES(CAST('pub_info' AS Varbinary),CAST('pr_info' AS Varbinary),CONVERT(float,GetDate()),0,@cpr_info,CAST(@cpub_id AS Varbinary))
END ELSE BEGIN
UPDATE StormMonitor SET LastCrc=CurrentCrc,CurrentCrc=@cpr_info,LastRun=CONVERT(float,GetDate()) WHERE Tablename = 'pub_info' AND ColoumName = 'pr_info' AND KeyField = @cpub_id AND CurrentCrc <> @cpr_info
END
-- ******* End field block, repeat for each field in table ************
FETCH NEXT FROM inserted_cursor INTO @cpub_id,@cpr_info
END
CLOSE inserted_cursor
DEALLOCATE inserted_cursor
The actual database where the trigger should be installed. In this example we use pubs, but you must change it to the actual database you are installing on.
The trigger name. Ir it unique in the database, so you might consider some semanthics where the tablename is part of it. tr_storm_pub_info could be a guess.
The name of the table on which you will monitor changes.
The field name inside the table where changes will be recorded.
Key field, which identifies the very record inside the table. It is used to identify the entry to backtrace changes.