Issue while disabling a trigger

Question

Post reply

Issue while disabling a trigger

Atif

Hall of Fame

Points: 3578
More actions
August 6, 2008 at 8:56 pm

#382170

In SSMS As i try to disable the trigger with following statement
DISABLE TRIGGER triggerName ON databasename..tablename
i get following error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TRIGGER'.
What may be reason for that
Take care
DBDigger Microsoft Data Platform Consultancy.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

The syntax looks correct. Make sure you do not have the code partially highlighted or you may get this error message.

Atif Hall of Fame Points: 3578 More actions · Answer 2

i have confirmed the highlight and syntax but can not find the reason 😉

DBDigger Microsoft Data Platform Consultancy.

Atif Hall of Fame Points: 3578 More actions · Answer 3

Now it worked in following syntax, but i wonder that why it did not work in previous syntax

alter table tableName

disable trigger TriggerName

DBDigger Microsoft Data Platform Consultancy.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

DDL statements do not usually accept a Database name.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 5

rbarryyoung (8/6/2008)
DDL statements do not usually accept a Database name.

I was able to disable a trigger on my test database using the dbname..tablename syntax. I wonder if it is a version thing.

happycat59 One Orange Chip Points: 29389 More actions · Answer 6

Is your database set to compatability level 80 (SQL 2000) ?

Atif Hall of Fame Points: 3578 More actions · Answer 7

yes it is at compatability level 80. So that may be reason?

DBDigger Microsoft Data Platform Consultancy.

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 8

Disable Statement accepts Database Name. The syntax is correct if the table on which you are performing Disable Trigger operation is in the dbo schema. But in this case, the error should be different.

BUT I am unable to re-generate the error in Query window.

Are you trying to disable triggers in a stored procedure? If this is the case, try using ';' after each Disable statement.

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Atif Hall of Fame Points: 3578 More actions · Answer 9

i tried this statement standalone in SSMS. Database compatibility level is 80.

DBDigger Microsoft Data Platform Consultancy.

happycat59 One Orange Chip Points: 29389 More actions · Answer 10

DISABLE TRIGGER is a SQL 2005 command. If your database compatability is below 90, it is not supported. You will need to use the equivalent ALTER command instead.

If you don't have a requirement to remain in 80 compatability, change it to 90 and you can use the DISABLE TRIGGER command.