before update trigger - 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)

before update trigger

Author

Topic

RB2K
Starting Member

4 Posts

Posted - 2008-04-14 : 04:52:31

Hi,

I'm trying to write an update trigger on sql 2005 but having a few issues. Basically I have a table with say 10 records in there. When an update occurs on a record and I want the trigger to recognise this and place a flag of X in a column to mark it.

The trigger then copies the record marked with X into another table. In the source table where the flag is marked X it will then update it with a GetDate function.

Can someone advise what I'm doing stupidly wrong. I want a before update function. But I'm getting an error on the Before Syntax. Any advise/help appreciated

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Update_Flag]
ON [MYDB].[dbo].[SOURCETABLE]
BEFORE UPDATE
AS
Update [SOURCETABLE]
Set RecordUpdateFlag = 'X'

Update [SOURCETABLE]
Set RecordUpdateType='U' -- For Record Update

Insert into MYDB.dbo.[xSOURCETABLE]
Select * from [SOURCETABLE]
where RecordUpdateFlag = 'X'
and RecordUpdateType='U'

Update [SOURCETABLE]
Set RecordUpdateFlag= GetDate()

Update [xSOURCETABLE]
Set RecordUpdateFlag= GetDate()

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 05:17:19

I don't think BEFORE is a valid keyword when defining trigger in SQL Server 2005. However, you can use INSTEAD OF trigger.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:19:24

Why a BEFORE update trigger?

CREATE TRIGGER dbo.trgUpdateFlag ON MyDB.dbo.SourceTable AFTER UPDATE
AS

UPDATE		t
SET		t.RecordUpdateFlag = GETDATE(),
		t.RecordUpdateType = 'U'
FROM		SourceTable AS t
INNER JOIN	inserted AS i ON i.PkCol = t.PkCol

INSERT		MyDB.dbo.xSourceTable
		(
			Col1,
			Col2,
			... ,
			RecordUpdateFlag,
			RecordUpdateType,
			...
		)
SELECT		Col1,
		Col2,
		... ,
		GETDATE() AS RecordUpdateFlag,
		'U' AS RecordUpdateType,
		...
FROM		inserted

E 12°55'05.25"
N 56°04'39.16"

RB2K
Starting Member

4 Posts

Posted - 2008-04-14 : 05:43:35

The reason for the before update command is unique. I basically when to identify and update a single record within a table. For example I have a source table of ten records. Once a record is being modified at run time I want to flag that. So when my trigger runs I can carry out my select query based upon that record being different to the rest. I don't want to update all records within a table and copy then to another. Only ones which have changed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:52:14

Yes, this is EXACTLY what I have done in my suggestion.
Just replace ".PkCol" with the name of the real primary key column.

Also, you have 5 DML statements, whereas I only use 2. This would suggestion my suggestion will run faster with less probability to create locks.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:58:04

quote:

Originally posted by RB2K

CREATE TRIGGER [dbo].[Update_Flag] 
ON [MYDB].[dbo].[SOURCETABLE]
BEFORE UPDATE 
AS 
Update [SOURCETABLE]
Set RecordUpdateFlag = 'X'

Update [SOURCETABLE]
Set RecordUpdateType='U' -- For Record Update

Insert into MYDB.dbo.[xSOURCETABLE]
Select * from [SOURCETABLE]
where RecordUpdateFlag = 'X' 
and RecordUpdateType='U'

Update [SOURCETABLE]
Set RecordUpdateFlag= GetDate()

Update [xSOURCETABLE]
Set RecordUpdateFlag= GetDate()

The statement

Update [xSOURCETABLE]
Set RecordUpdateFlag= GetDate()

is "stupidly" wrong, because that table has no informatio about what is being inserted in the current table.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources