How to write a Trigger in SQL Server

Just a quick post with a simple example trigger I put together. I’ve noticed allot of people seem to think that cursors are necessary to write a trigger, not so. In fact cursors are a bad way of doing things in general. They’re slow and run one command at a time, they loose out on the speed increase of running an update in batch. Al-Farooque Shubho touches on this concept in his article “Understanding “Set based” and “Procedural” approaches in SQL”.

Here’s the trigger, it’s written to audit changes on a date field. You need to create a table to handle the insert for this. Remember two things here:
1) The new table needs to have exactly the same data types (and null ability) of the parent table, but NOT the default values.
2) The primary key of the origin table will NOT be the same in the audit table. This is because a row can be changed multiple times, auditing those changes means the same primary key being inserted over and over. In my example I create a new column called EventOccuranceTime which is defaulted to getdate(). I then used the primary tables unique identifier plus the EventOccuranceTime as the PK in the new table. Note that triggers can seriously slow down your data access, putting a primary key on the audit table will slow down updates / inserts into the parent table more yet. If this speed reduction is an issue for you you may not want a primary key on the audit table, just take the hit when reading from the data (no index) instead of writing it with an index. I’ve included the table create script below as well:

CREATE TABLE [dbo].[SomeTable_audit](
	[ROWID] [int] NOT NULL,
	[EventOccuranceTime] [datetime] NOT NULL,
	[FromDATE] [datetime] NULL,
	[ToDATE] [datetime] NULL,
 CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED 
(
	[ROWID] ASC,
	[EventOccuranceTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SomeTable_audit] ADD  CONSTRAINT [DF_SomeTable_audit_EventOccuranceTime]  DEFAULT (getdate()) FOR [EventOccuranceTime]
GO

Something to know about triggers, they have two special tables available in them. One is the rows inserted, the other the rows deleted. If you made updates then they are tables that hold all the rows that were updated (treating old data as deleted and new data as inserted), even if only one column was updated.
If you change this trigger to include inserts and deletes the join below will thwart that. You will need to change this join to allow left and / or right joins depending on inserts / deletes, or write multiple queries, one for inserts, one for deletes, one for updates. You can write more than one query in a single trigger.

create trigger dbo.audit_SomeTable on dbo.SomeOriginTable for update -- This could include insert and delete, mine only handles updates.
as
if update(desired_date) --if the desired_date field is changing do the insert
begin
--Code thanks to http://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
	insert into dbo.SomeTable_audit (rowid,FromDATE,ToDATE) 
	select i.rowid,d.desired_date,i.desired_date
		from inserted i
	join deleted d on
		i.rowid = d.rowid 
	where datediff(day,d.desired_ship_date,i.desired_ship_date)<>0 --added because update() doesn't always seem to work, 
	--I believe it determines it's being updated if it's passed in the query, not if it's actually different.
end
go

Here's a great MSDN magazine article with more examples of triggers (including triggers that prevent certain kinds of updates):
Exploring SQL Server Triggers.

About these ads
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: