Unable to drop trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Unable to drop trigger

Author

Topic

kkrishna
Starting Member

23 Posts

Posted - 2009-07-09 : 18:23:38

I created a trigger that will throw a message whenever a new record is inserted in the table. Now I want to remove this trigger. I am not able to remove.

CREATE TRIGGER prod_culture_trig ON Production.Culture
AFTER INSERT
AS
SELECT 'New culture entry added';

I get the following error message:

Msg 2714, Level 16, State 2, Procedure prod_oulture_trig, Line 4
There is already an object named 'prod_oulture_trig' in the database.

This error confirms that we have the trigger already existing. Now I run the code
DROP TRIGGER prod_oulture_trig;
I get the following error message -
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.

What permission do I require? This is a test database on my computer with me as the administrator.

How to remove this trigger?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-07-09 : 19:56:30

Isn't the o supposed to be a c?

DROP TRIGGER prod_oulture_trig;

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

kkrishna
Starting Member

23 Posts

Posted - 2009-07-10 : 10:32:51

Thanks for the correction, Ajarn.
But with the corrected code also, this does not work. When I run the code:

DROP TRIGGER prod_culture_trig;

I get the error:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.

I know that it exists, because when I run the code:

CREATE TRIGGER prod_culture_trig ON Production.Culture
AFTER INSERT
AS
SELECT 'New culture entry added';

I get the errror:
Msg 2714, Level 16, State 2, Procedure prod_culture_trig, Line 4
There is already an object named 'prod_culture_trig' in the database.

Ganesh00
Starting Member

1 Post

Posted - 2009-07-10 : 11:03:18

Krishna

Who is the owner of the this object and check the permission.
It might owned by someone other the dbo or vice versa.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-10 : 11:21:39

quote:
Originally posted by kkrishna

Thanks for the correction, Ajarn.
But with the corrected code also, this does not work. When I run the code:

DROP TRIGGER prod_culture_trig;

I get the error:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.

I know that it exists, because when I run the code:

CREATE TRIGGER prod_culture_trig ON Production.Culture
AFTER INSERT
AS
SELECT 'New culture entry added';

I get the errror:
Msg 2714, Level 16, State 2, Procedure prod_culture_trig, Line 4
There is already an object named 'prod_culture_trig' in the database.

run this and check whats the source schema.then see whether you've permissions to it

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 11:21:54

Maybe it's not compiled as a trigger. What does this return?


declare @objName varchar(25)
set @objName = 'prod_culture_trig'
select objectproperty(object_id(@objName), 'isTrigger') isTrigger
       ,objectproperty(object_id(@objName), 'ownerid') ownerID

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 11:31:15

first check that the object exists


select * from sysobjects where name = 'prod_culture_trig'

if exists, what's the xtype or type return from the above query ?

if not exists then there is no object with that name

KH
[spoiler]Time is always against us[/spoiler]

kkrishna
Starting Member

23 Posts

Posted - 2009-07-10 : 18:07:14

I ran the query as suggested:
select * from sysobjects where name = 'prod_culture_trig'
The object does exist. xtype is TR and type is also TR.
Object id - 199671759; uid - 7; parent_obj - 565577053; schema_ver - 0; stats_schema_ver - 0; etc.

I ran the code suggested by TG and I get the following results:
isTrigger NULL
OwnerID NULL

Well, I created a trigger and this gave the specified output. If it is not a trigger, what is it then?
If it is an object, how do I remove it?

Krishna

revdrwebb
Starting Member

1 Post

Posted - 2009-07-10 : 19:52:23

Since:
DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ; ]

Why not try

drop TRIGGER production.prod_culture_trig

... and then again, I might be wrong.

kkrishna
Starting Member

23 Posts

Posted - 2009-07-10 : 21:37:11

I tried DROP TRIGGER Production.Culture.prod_culture_trig; where the Production.Culture is the name of the table.
I get the error message:
Msg 166, Level 15, State 1, Line 1
'DROP TRIGGER' does not allow specifying the database name as a prefix to the object name.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-11 : 07:58:20

hmmm - odd that select from sysobjects found it by objectproperty didn't...That may indicate that it's a permission issue. Perhaps you were connected under a different security context when you created it than you were when you tried to delete it.

Are you a DBO in that database or can you log in as SA? Confirm that you are in the correct database when you attempt to drop it. Can you see it in Object Explorer? If so cam you right-click | DELETE?

Be One with the Optimizer
TG

benjamin_moskovits
Starting Member

2 Posts

Posted - 2009-09-16 : 13:25:46

The way to drop a database trigger is to issue a

drop trigger triggername ON DATABASE

so you may want to try

drop trigger [prod_culture_trig] ON DATABASE

Ben Moskovits

benjamin_moskovits
Starting Member

2 Posts

Posted - 2009-09-16 : 13:31:56

Sorry for the previous response. That will work for a database trigger. For the trigger you have this will work:

DROP TRIGGER [Production].[prod_culture_trig]

Benjamin Moskovits

tienpv
Starting Member

1 Post

Posted - 2010-11-16 : 22:41:01

quote:
Originally posted by kkrishna

I created a trigger that will throw a message whenever a new record is inserted in the table. Now I want to remove this trigger. I am not able to remove.

CREATE TRIGGER prod_culture_trig ON Production.Culture
AFTER INSERT
AS
SELECT 'New culture entry added';

I get the following error message:

Msg 2714, Level 16, State 2, Procedure prod_oulture_trig, Line 4
There is already an object named 'prod_oulture_trig' in the database.

This error confirms that we have the trigger already existing. Now I run the code
DROP TRIGGER prod_oulture_trig;
I get the following error message -
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.

What permission do I require? This is a test database on my computer with me as the administrator.

How to remove this trigger?

That is wrong.
You must try: DROP TRIGGER Production.prod_culture_trig
not DROP TRIGGER prod_culture_trig
wish Command completed successfully.

ssivaprasad
Starting Member

9 Posts

Posted - 2011-05-11 : 03:23:42

If the trigger is DATABASE Level scope use below

Drop TRIGGER connection_limit_trigger on Database

Otherwise if it is Server Level scope please try below;

Drop TRIGGER connection_limit_trigger on all server

Sivaprasad S - SIVA

http://sivasql.blogspot.com

Sedecimdies
Starting Member

1 Post

Posted - 2013-09-18 : 23:49:49

you should specify the scheme on with it was created

DROP TRIGGER prod_oulture_trig;

will fail

DROP TRIGGER dbo.prod_oulture_trig;

will drop the trigger if it was create under the dbo scheme

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources