Bizarre error on ALTER TRIGGER 1

[LFCfan]

SQL2K5

The error is:
Msg 208, Level 16, State 6, Procedure tr_myTable_INSERT, Line 33
Invalid object name 'tr_myTable_INSERT'.

ALTER TRIGGER tr_myTable_INSERT
ON [mySchema].[myTable]
FOR INSERT
AS
.....
[i](line 33)[/i] INSERT INTO mySchema.myOtherTable SELECT ...
.....

Now, myOtherTable has no triggers on it, and certainly not this trigger.
The trigger's name in the ALTER TRIGGER statement is definitely the right one, I am not trying to alter a non-existing trigger.

The change I made was literally tiny - I changed a WHERE clause from "where myColumn = @variable1" to "where myColumn = @variable2"

Any thoughts? I'm sure I'm missing something blindingly obvious, but I'm stumped!

Thank you

~LFCfan

 

[bborissov]

You are not in the right Database.
Try with:

USE YourDataBaseWhereThatTriggerExists
GO
ALTER TRIGGER .....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LFCfan]

Thanks Borislav, but I am definitely in the right database.


~LFCfan

 

[gmmastros]

Triggers are owned by schema's.

Try...

sp_helptrigger 'YourTableNameHere'

You should see a 'trigger_owner' column.

ALTER TRIGGER [red][MySchema].[/red]tr_myTable_INSERT
etc...

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LFCfan]

Thank you George, that was it.

I presumed that, by scripting the trigger via Management Studio (Right click on trigger/Modify), it would give me a correct script. Silly me.

Confusing error message though - I would have expected something more along the lines of "cannot alter trigger xxx as it does not exist on the table"

~LFCfan

 

[gmmastros]

I presumed that, by scripting the trigger via Management Studio (Right click on trigger/Modify), it would give me a correct script. Silly me.

I think there is a setting for that!

Open SQL Server Management Studio.
Click Tools -> Options
Click 'Scripting' item in the list (on the left).
On the right, you will see 'Object scripting options'
Scroll down to 'Schema qualify object names'

I suspect this is set to 'false' for you. I recommend you double click the word 'false' (changing it to true).




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LFCfan]

Odd, I don't get the 'Scripting' item.

Anything else I script in this way (tables, stored procs) is fully qualified with schema name, it's only the trigger that seems to have been scripted "orphaned"!

~LFCfan