W.O.P.R   W.O.P.R STORM monitor - MS SQLServer - Creation of the triggers   Help

Home/storm.monitor/mssql/mssql.create.trigger.asp

Creation of the trigger.

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.