Trigger question - 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 2005 Forums

Transact-SQL (2005)

Trigger question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-01-15 : 09:51:19

I have two tables:

Clients
-------

with columns:

ClientID int,
StatusID int,
Surname varchar(35),
Forename varchar(35)

Audit
-----

AuditID (int, Pk, identity)
StatusID int,
OldStatusID int,
TimeStamp (timestamp)

When the statusID of table 'Clients' is updated I want to insert a record into the 'Audit' table so that the original StatusID and the new (updated) StatusID are inserted.

How do I do this please?

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 10:03:32

Trigger ?


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MyTrigger' AND type = 'TR')
	DROP TRIGGER dbo.MyTrigger
GO
CREATE TRIGGER dbo.MyTrigger
ON dbo.Clients
/* WITH ENCRYPTION */
AFTER INSERT, UPDATE	--, DELETE
AS
/*
 * MyTrigger	Trigger for Audit of Status
 *
 * HISTORY:
 *
 * 15-Jan-2010 Started
 */
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

 	INSERT dbo.Audit
	(
		SomeClientIDColumn, StatusID, OldStatusID
	)
 	SELECT	
		[ClientID] = COALESCE(I.ClientID, D.ClientID),
		[StatusID] = I.StatusID,
		[OldStatusID] = D.StatusID
 	FROM	inserted AS I
 		FULL OUTER JOIN deleted AS D
 			 ON D.ClientID = I.ClientID
 	WHERE 	COALESCE(I.StatusID, 0) <> COALESCE(D.StatusID, 0)

--================== MyTrigger ==================--
GO
PRINT 'Create trigger MyTrigger - DONE'
GO
--

This will create an Audit row on initial Insert to Clients table. If you don't want that remove INSERT from the AFTER ... line

Edit: Changed AuditID column to indicate a required new ClientID column; modified to work as any of Insert, Update, Delete trigger - in case that is needed rather than just an AFTER UPDATE trigger.

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 10:10:55

Did you really mean to use a timestamp datatype "TimeStamp (timestamp)", or did you want "Current date and time" ?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-01-15 : 10:12:46

Many thanks Kirsten. Much appreciated (I'm new to triggers).

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:13:18

Something like this..

CREATE TRIGGER [Clients_Update]
ON [dbo].[Clients]
AFTER UPDATE
AS
BEGIN
IF UPDATE(StatusID)
	BEGIN
	  INSERT INTO [dbo].[Audit]
	  SELECT b.StatusID,a.StatusID,getdate()
          FROM deleted a INNER JOIN inserted b
          on a.ClientID = b.ClientID
	END
END

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:15:14

Oops..too late on that one...Dint refresh my page at all.

Kristen, Since his AuditID is an identity, maybe this is not needed?

[AuditID] = I.ClientID

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 10:38:06

"Since his AuditID is an identity, maybe this is not needed?"

You are right. In that case he'll need a separate ClientID column in his Audit table - otherwise the Status Before/After data won;t have much "context"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:40:49

Yeah..exactly...he needs the ClientID in the Audit tbl...
or..maybe he already has and dint show it to us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources