Can''t drop trigger assembly

Created an assembly based on this article...

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Now I want to make changes and redeploy the assembly but when I try to drop I get this.

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'AuditCommon')

DROP ASSEMBLY [AuditCommon]

Msg 6590, Level 16, State 1, Line 3

DROP ASSEMBLY failed because 'AuditCommon' is referenced by object 'clr_trigger'.

Any ideas?

Figured it out. clr_trigger was a trigger that I created somewhere along the way which was dependent upon the assembly. It didn't appear anywhere in SQL Manager Studio so I thought that it was some sort of system object.|||A late reply,

using show dependencies, list out those need to removed. Remove the listed first and then drop the assembly.

Can't drop trigger assembly

Created an assembly based on this article...

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Now I want to make changes and redeploy the assembly but when I try to drop I get this.

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'AuditCommon')

DROP ASSEMBLY [AuditCommon]

Msg 6590, Level 16, State 1, Line 3

DROP ASSEMBLY failed because 'AuditCommon' is referenced by object 'clr_trigger'.

Any ideas?

Figured it out. clr_trigger was a trigger that I created somewhere along the way which was dependent upon the assembly. It didn't appear anywhere in SQL Manager Studio so I thought that it was some sort of system object.|||A late reply,

using show dependencies, list out those need to removed. Remove the listed first and then drop the assembly.

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

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." Bu
t
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 th
e
> 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 synta
x
> 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:
>
>|||> 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 w
hoever 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...[vbcol=seagreen]
> 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:
>|||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

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

can't delete a row from primary table - is there an SP for thi

Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
updates and there was a trigger to rollback deletes. But I commented out al
l
of the triggers - all of them. So there are basically no triggers except fo
r
t_sometinging on PrimaryTbl
For Insert...
As
/* */
Return
I did that to all of the triggers so that nothing would fire. Right now I
am recreating the table with all the triggers, indexes, relationships,
constraints and so on and see if that table works (minus of course, the For
Delte As Rollback...)
"Tibor Karaszi" wrote:

> Jeff,
> As of 7.0 you can have several triggers of the same type on a table. And a
s of 2000, you can define
> which to fire first and which to fire last.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jeff Dillon" <jeffdillon@.hotmail.com> wrote in message
> news:OqFSPqreGHA.5040@.TK2MSFTNGP03.phx.gbl...
>
>A much easier way to disable triggers, that doesn't involve having to change
code, is to use ALTER TABLE ... DISABLE TRIGGER
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
> all
> of the triggers - all of them. So there are basically no triggers except
> for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the
> For
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||recreating the table seemed to do the trick. I was able to delete the row
from the re-created table.
Recreating the live table will be a bigger hassel because I will have to
stop replication first.
"Rich" wrote:
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
all
> of the triggers - all of them. So there are basically no triggers except
for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the Fo
r
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||yes, I have used that before, but I just needed to make sure there was
nothing in the triggers that could possibly run.
Well, as fate would have it, after I recreated my table and was able to
delete the desired row, I then readded the row and also re-added all the
triggers, relationships, etc. Now I can't delete the row again in the new
table. So I guess maybe there was nothing wrong with the original table.
So tommorrow I have to play the boring game of recreating the table, add the
row, delete the row, and keep adding triggers one at a time and relationship
s
until I can't delete the row and thus isolate the offending procedure,
relationship. I am already having indigestion thinking about it.
"Kalen Delaney" wrote:

> A much easier way to disable triggers, that doesn't involve having to chan
ge
> code, is to use ALTER TABLE ... DISABLE TRIGGER
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
>
>|||Well, I figured out what the problem was in deleteing rows on my table. It
was the Instead Of Delete trigger. Even though I had commented out the body
and only had
Create Trigger...
Instead Of Delete
As
--
--
Return
This would not allow deletes on the table. When I removed the trigger
entirely, I was able to delete rows.
"Rich" wrote:
> yes, I have used that before, but I just needed to make sure there was
> nothing in the triggers that could possibly run.
> Well, as fate would have it, after I recreated my table and was able to
> delete the desired row, I then readded the row and also re-added all the
> triggers, relationships, etc. Now I can't delete the row again in the new
> table. So I guess maybe there was nothing wrong with the original table.
> So tommorrow I have to play the boring game of recreating the table, add t
he
> row, delete the row, and keep adding triggers one at a time and relationsh
ips
> until I can't delete the row and thus isolate the offending procedure,
> relationship. I am already having indigestion thinking about it.
> "Kalen Delaney" wrote:
>|||I think that is because the code in the trigger runs in place of your delete
statement (on the rows affected by it). Since there was no code, and no
action to perform, nothing was done with these rows.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:92D437D9-CFDB-45F4-8991-90C49BBFD1E7@.microsoft.com...
> Well, I figured out what the problem was in deleteing rows on my table.
It
> was the Instead Of Delete trigger. Even though I had commented out the
body
> and only had
> Create Trigger...
> Instead Of Delete
> As
> --
> --
> Return
> This would not allow deletes on the table. When I removed the trigger
> entirely, I was able to delete rows.
> "Rich" wrote:
>
new
table.
the
relationships
change
some
commented out
except
now I
relationships,
the
table. And
UPDATE,
I
delete/remove a
of
the
test
was
need to
from
is

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GO
UPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>
|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
----
--
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create INSTEAD OF trigger

I wanted to learn how to do this, so I created the following non-updatable
view:
CREATE VIEW [dbo].[TestAggregateView]
WITH SCHEMABINDING, VIEW_METADATA
AS
SELECT C.CustId, C.CrLmt, SUM(A.OrigDocAmt) AS TotAmt
FROM dbo.Customer AS C INNER JOIN
dbo.ARDoc AS A ON A.CustId = C.CustId
GROUP BY C.CustId, C.CrLmt
I wrote this UPDATE statement, which fails:
UPDATE TestAggregateView SET
CrLmt = 20000
WHERE CrLmt = 0
AND TotAmt > 10000
The goal is to create a trigger that will run a different and valid update
statement. Here is the code I tried:
CREATE TRIGGER [dbo].[TestAggregateView_Instead_Update]
ON [dbo].[TestAggregateView]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Customer SET
CrLmt = 20000
WHERE Customer.CustID = inserted.CustID
END
The error I get is:
Msg 4104, Level 16, State 1, Procedure TestAggregateView_Instead_Update,
Line 14
The multi-part identifier "inserted.CustID" could not be bound.
Can someone tell me what I'm doing wrong? I could not find a sample INSTEAD
OF UPDATE trigger to compare this with, but according to the sample INSTEAD
OF INSERT, inserted is the valid name for the temporary table, and I think it
should hold all the records that meet the select requirements.Here's the problem:
UPDATE Customer SET
CrLmt = 20000
WHERE Customer.CustID = inserted.CustID
You refer to a tablme named INSERTED in the WHERE clause but you haven't listed it in the "FROM"
clause (I put FROM in quptes since UPDATE according to ANSI SQL doesn't have a FROM clause).
So the problem is with your UPDATE inside your trigger, not with the trigger per se. You have to
re-write that UPDATE so it is a valid UPDATE statement. For instance
UPDATE Customer
SET CrLmt = 20000
WHERE Customer.CustID IN (SELECT CustID FROM inserted)
Or:
UPDATE Customer
SET CrLmt = 20000
WHERE EXISTS(SELECT * FROM inserted AS i WHERE i.CustID = Customer.CustID )
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:F1BAB94D-ECFE-4BF7-B1EB-1330FFD17B0F@.microsoft.com...
>I wanted to learn how to do this, so I created the following non-updatable
> view:
> CREATE VIEW [dbo].[TestAggregateView]
> WITH SCHEMABINDING, VIEW_METADATA
> AS
> SELECT C.CustId, C.CrLmt, SUM(A.OrigDocAmt) AS TotAmt
> FROM dbo.Customer AS C INNER JOIN
> dbo.ARDoc AS A ON A.CustId = C.CustId
> GROUP BY C.CustId, C.CrLmt
> I wrote this UPDATE statement, which fails:
> UPDATE TestAggregateView SET
> CrLmt = 20000
> WHERE CrLmt = 0
> AND TotAmt > 10000
> The goal is to create a trigger that will run a different and valid update
> statement. Here is the code I tried:
> CREATE TRIGGER [dbo].[TestAggregateView_Instead_Update]
> ON [dbo].[TestAggregateView]
> INSTEAD OF UPDATE
> AS
> BEGIN
> SET NOCOUNT ON;
> UPDATE Customer SET
> CrLmt = 20000
> WHERE Customer.CustID = inserted.CustID
> END
> The error I get is:
> Msg 4104, Level 16, State 1, Procedure TestAggregateView_Instead_Update,
> Line 14
> The multi-part identifier "inserted.CustID" could not be bound.
> Can someone tell me what I'm doing wrong? I could not find a sample INSTEAD
> OF UPDATE trigger to compare this with, but according to the sample INSTEAD
> OF INSERT, inserted is the valid name for the temporary table, and I think it
> should hold all the records that meet the select requirements.
>