Trigger error - invalid object name on non-existant trigger 1

[iwease]

Hi,

I created a trigger on a database once called EN_OT_NodeStructure_InsertTrigger. I have since dropped the trigger and it appears to be gone since

IF OBJECT_ID ('EN_OT_NodeStructure_InsertTrigger', 'TR') IS NOT NULL
SELECT 1
else
SELECT 0

returns 0.

However, when I go into the table (via the enterprise manager) and try to insert a record, i get the error

Invalid object name 'EN_OT_NodeStructure_InsertTrigger'

Any ideas?

 

[jbenson001]

Have you refeshed the list of tables in EM?

 

[SQLDenis]

what happes when you run this from Query Analyzer?

Change TableName to your table's name

EXEC sp_helptrigger TableName


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[iwease]

First, just wanted to say thanks for your help. This is what I've tried.

-I've tried refreshing the tables

-I've tried recreating the table (and a different trigger in a different database) ...but it still says Invalid object name 'EN_OT_NodeStructure_InsertTrigger'

-I've tried deleting the original database, and then recreating it without ever creating 'EN_OT_NodeStructure_InsertTrigger' (I do have a trigger that is supposed to have the same functionality but it is named 'EN_OT_NodeStructure_INSERT'


When I run that query, I get one row

EN_OT_NodeStructure_INSERT
dbo 0 0 1 1 0

 

[SQLDenis]

looks like you do have an insert trigger

what happens when you run this?

select name from sysobjects where xtype ='tr'

grab the name then run this, change the name if it is different

sp_helptext 'EN_OT_NodeStructure_InsertTrigger'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[iwease]

select name from sysobjects where xtype ='tr'
gives

EN_OT_NodeStructure_INSERT




and
sp_helptext 'EN_OT_NodeStructure_InsertTrigger'
gives

Server: Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 52
The object 'EN_OT_NodeStructure_InsertTrigger' does not exist in database 'AKAEnterprise'.




and
sp_helptext 'EN_OT_NodeStructure_INSERT'
gives the code for my new trigger that I am trying to use

CREATE TRIGGER EN_OT_NodeStructure_INSERT
ON EN_OT_NodeStructure
FOR INSERT AS
BEGIN

UPDATE child
SET depth = ISNULL(parent.depth + 1,0),
lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.id)) + '/'
FROM EN_OT_NodeStructure child
INNER JOIN
inserted i ON i.NodeStructureID = child.NodeStructureID
LEFT OUTER JOIN
EN_OT_NodeStructure_InsertTrigger parent
ON child.fk_ParentID=parent.NodeStructureID
END

 

[iwease]

I'm using the msde. Perhaps I need a patch or update or something.

I meant to say, I've also tried changing the name of

EN_OT_NodeStructure_INSERT back to EN_OT_NodeStructure_InsertTrigger but it still gives me the invalid object error

 

[SQLDenis]

here is your problem

LEFT OUTER JOIN
[red]EN_OT_NodeStructure_InsertTrigger[/red] parent
ON child.fk_ParentID=parent.NodeStructureID

you are joining on a non-existing trigger(???) instead on a table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[iwease]

my god...sometimes I am so blind. I've been staring at the computer for waaaaaaaaaaay to long. It was particularly annoying because I messed up in such a way that I was using a trigger name for a table so it was really confusing me about triggers. Anyway, thank you so much for the fresh set of eyes and your help.

I really really appreciate it