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? ThanksJen 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:
> > 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
>|||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:
> > 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
> 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:
> > > 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
> > MCDBAhttp://www.realsqlguy.com- Hide quoted text -
> >
> > - Show quoted text -
>
>|||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:
> > 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:
> > > > 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
> > > MCDBAhttp://www.realsqlguy.com-Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - 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:
> > 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:
> > > > > 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
> > > > MCDBAhttp://www.realsqlguy.com-Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>|||> Even though I specify "dbo.trigger_name" in create
> trigger, but the trigger's owner is "userA" by checking sysobjects table. Is
> it strange?
Yes, that is strange. If you can provide a repro, we can submit a bug report. If you create an
object, specifying dbo, then that object will be owned by dbo, or you would get an error (if you
don't have permissions to do so). Unless you do something like:
CREATE TRIGGER [dbo.mytrigger]
CREATE TRIGGER "dbo.mytrigger"
Now the name of the trigger will be "dbo.mytrigger" and the owner would be whoever created the
trigger.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:CD8044B8-FD3D-4759-8437-EB974F1E0017@.microsoft.com...
> 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:
>> > 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:
>> > > > > 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
>> > > > MCDBAhttp://www.realsqlguy.com-Hide quoted text -
>> >
>> > > > - Show quoted text -- Hide quoted text -
>> >
>> > - Show quoted text -
>>|||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
MCDBAsql