can't drop trigger

Hi,
I just created a tirgger. when I tried to drop it, it say "Cannot drop the
trigger 'trigger_name', because it does not exist in the system catalog." But
it shows when I query as:
SELECT *
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trigger_name'
when I tried to create again, it failed "There is already an object named
'trigger_name' in the database.
what can I do? Thanks
Jen wrote:
> Hi,
> I just created a tirgger. when I tried to drop it, it say "Cannot drop the
> trigger 'trigger_name', because it does not exist in the system catalog." But
> it shows when I query as:
> SELECT *
> FROM sysobjects o INNER JOIN syscomments c
> ON o.id = c.id
> WHERE o.type = 'TR' and o.name = 'trigger_name'
> when I tried to create again, it failed "There is already an object named
> 'trigger_name' in the database.
> what can I do? Thanks
You should always used schema-qualified object names, i.e.
"dbo.MyTriggerName". Sounds like you're dealing with a schema-naming
issue, "dbo.MyTriggerName" vs. "userA.MyTriggerName".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I used "create trigger dbo.trigger_name on userA.table_name..."
I tried to user "sp_helptrigger userA.table_name" it says "Incorrect syntax
near '.'"
How can I drop the trigger? How can I view the trigger? Thanks
"Tracy McKibben" wrote:

> Jen wrote:
> You should always used schema-qualified object names, i.e.
> "dbo.MyTriggerName". Sounds like you're dealing with a schema-naming
> issue, "dbo.MyTriggerName" vs. "userA.MyTriggerName".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||is it in SQL 2000 or 2005
try specifying someting like
Drop Trigger <TriggerName>
ON <Database or ALL Server>
Maninder
MCDBA
On Jan 30, 1:54 pm, Tracy McKibben <t...@.realsqlguy.com> wrote:
> Jen wrote:
>
>
> You should always used schema-qualified object names, i.e.
> "dbo.MyTriggerName". Sounds like you're dealing with a schema-naming
> issue, "dbo.MyTriggerName" vs. "userA.MyTriggerName".
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com- Hide quoted text -
> - Show quoted text -
|||It's sql2000. I got "Incorrect syntax near the keyword 'on'".
"Maninder" wrote:

> is it in SQL 2000 or 2005
> try specifying someting like
> Drop Trigger <TriggerName>
> ON <Database or ALL Server>
> Maninder
> MCDBA
> On Jan 30, 1:54 pm, Tracy McKibben <t...@.realsqlguy.com> wrote:
>
>
|||Jen wrote:
> I used "create trigger dbo.trigger_name on userA.table_name..."
> I tried to user "sp_helptrigger userA.table_name" it says "Incorrect syntax
> near '.'"
>
sp_helptrigger 'userA.table_name'
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||As Tracy suggestd, try using the Object owner in the drop statement.
Or if you are db_owner or a sa you can drop the Trigger with the
Schemaname.Triggername
Maninder
MCDBA
On Jan 30, 2:23 pm, Jen <J...@.discussions.microsoft.com> wrote:
> It's sql2000. I got "Incorrect syntax near the keyword 'on'".
>
> "Maninder" wrote:
>
>
>
>
> - Show quoted text -
|||Thanks. I droped it. Even though I specify "dbo.trigger_name" in create
trigger, but the trigger's owner is "userA" by checking sysobjects table. Is
it strange?
Thanks
"Maninder" wrote:

> As Tracy suggestd, try using the Object owner in the drop statement.
> Or if you are db_owner or a sa you can drop the Trigger with the
> Schemaname.Triggername
> Maninder
> MCDBA
>
> On Jan 30, 2:23 pm, Jen <J...@.discussions.microsoft.com> wrote:
>
>
|||We had the same Problem with the windows user accounts.
I had a Workaround for this problem.
I Created a Trigger on the Database for Events we need to capture like
Create_table,Create_procedure
So,every time a user creates a object this trigger Fires and renames
the schema to dbo for the object.
or Use ALTER SCHEMA for single use.
Maninder
MCDBA