Error on BEFORE UPDATE Trigger

Question

Error on BEFORE UPDATE Trigger

TcW_1978

SSCrazy

Points: 2018
More actions
April 9, 2009 at 2:20 pm

#132684

I got this simple TSQL line to create a trigger.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trig_taux
ON Taux_BL
BEFORE UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
And I receive this message :
Msg 195, Level 15, State 1, Procedure trig_taux, Line 3
'BEFORE' is not a recognized trigger.
The server doesn't seem to recognize the "BEFORE UPDATE", if I change it for AFTER UPDATE, it works, can someone help me? Isn't there a way to create a "BEFORE UPDATE" trigger?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Microsoft SQL Server does not have BEFORE TRIGGERS. You can emulate them using INSTEAD OF TRIGGERS. Check Books Online (BOL) for more information.

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

Be VERY careful if you use Instead of triggers. I worked for client that put lots of business logic in a Instead of Insert trigger. We encountered an issue when the translog was full and the system could not insert some transactions. We wanted to insert these manually and didn't realize the instead of trigger was there. We spent several days trying to unwind what it did. (In this case it disallowed them from using a bank card because the trigger "spent" all their money). Just a note of caution.

/soapbox off

Sean

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

I would recommend that you check out this article[/url] as well. It addresses several problems that I have seen on forums around triggers.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 4

You probably already know this, but make sure you can't achieve the same functionality with a check constraint - as that would function like a true "before" trigger.

TcW_1978 SSCrazy Points: 2018 More actions · Answer 5

Gabriel P (4/14/2009)
You probably already know this, but make sure you can't achieve the same functionality with a check constraint - as that would function like a true "before" trigger.

Didn't think of that, can you explain a bit?

Thanks

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 6

Check Constraints are good for validating field formatting or values.

For example:

SSN text field must have the format ###-##-####

An integer field must be within the values 0-30

A field cannot contain an empty string

What it's not good for is:

Sending an e-mail if a record is inserted

Copying a deleted record into a separate table

Cross checking the information inserted/updated with other tables

Specialized error handling (TRY/CATCH)

Check constraints are also done before the transaction actually begins. If it violates the constraint, it will throw an error and no transaction occurs. With a trigger, your trigger executes right before the transaction commits (which gives you the ability to rollback the transaction in the trigger).

Think of it as security of a building, with two layers of security - outside the building and inside of the building. The security outside uses a simpler model of filtering people out - not dressed accordingly, no id, etc - just high level visual information. The security inside the building has the capability of being more thorough (metal detectors, dogs, pat downs, etc), but if you reject that person they not only got to walk into the building, but they have to walk back out. You can also send them to a different room for further inspection. You can notify your superiors if it requires escalation. However this process is a lot less efficient, and all the while you're doing this, you have an open transaction that has not yet been committed or rolled back. Basically you might be holding up other people in line (blocking).