Drop hidden trigger - how

I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
charm. I then made a similar CLR trigger and deployed it to the
server. The T-SQL trigger seemed to disappear from the Database
Triggers folder for that database. However, when I delete a row from
the table I get e-mail from both the T-SQL trigger and the CLR trigger
in the Assembles folder. I would like to DROP the T-SQL trigger but it
is not visible in the object explorer. Any help on how to proceed.
Paul SullivanPaul,
Use the DROP TRIGGER T-SQL statement if refresh of objects doesn't show the
trigger.
HTH
Jerry
"Paul Sullivan" <paul-v-sullivanHATESPAM@.worldnet.att.net> wrote in message
news:4cudl15jj47engm5b9tt7dd66fmu71k1qn@.
4ax.com...
>I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
> charm. I then made a similar CLR trigger and deployed it to the
> server. The T-SQL trigger seemed to disappear from the Database
> Triggers folder for that database. However, when I delete a row from
> the table I get e-mail from both the T-SQL trigger and the CLR trigger
> in the Assembles folder. I would like to DROP the T-SQL trigger but it
> is not visible in the object explorer. Any help on how to proceed.
> Paul Sullivan|||Try looking at the output of: sp_helptrigger 'table_name'
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Paul Sullivan" <paul-v-sullivanHATESPAM@.worldnet.att.net> wrote in message
news:4cudl15jj47engm5b9tt7dd66fmu71k1qn@.
4ax.com...
>I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
> charm. I then made a similar CLR trigger and deployed it to the
> server. The T-SQL trigger seemed to disappear from the Database
> Triggers folder for that database. However, when I delete a row from
> the table I get e-mail from both the T-SQL trigger and the CLR trigger
> in the Assembles folder. I would like to DROP the T-SQL trigger but it
> is not visible in the object explorer. Any help on how to proceed.
> Paul Sullivan|||Hi Paul
Just check the link:
http://msdn.microsoft.com/library/d...br />
8wj6.asp
this might help you
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Paul Sullivan" wrote:

> I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
> charm. I then made a similar CLR trigger and deployed it to the
> server. The T-SQL trigger seemed to disappear from the Database
> Triggers folder for that database. However, when I delete a row from
> the table I get e-mail from both the T-SQL trigger and the CLR trigger
> in the Assembles folder. I would like to DROP the T-SQL trigger but it
> is not visible in the object explorer. Any help on how to proceed.
> Paul Sullivan
>|||Thank you, thank you, etc
Thanks to you particularly, Itzik Ben-Gan, since I didn't remember the
exact trigger name.
Trigger is now history
Paul Sullivan
On Wed, 19 Oct 2005 22:05:21 -0400, Paul Sullivan
<paul-v-sullivanHATESPAM@.worldnet.att.net> wrote:

>I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a
>charm. I then made a similar CLR trigger and deployed it to the
>server. The T-SQL trigger seemed to disappear from the Database
>Triggers folder for that database. However, when I delete a row from
>the table I get e-mail from both the T-SQL trigger and the CLR trigger
>in the Assembles folder. I would like to DROP the T-SQL trigger but it
>is not visible in the object explorer. Any help on how to proceed.
>Paul Sullivan

Drop Column with default constraint in T-SQL

Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint nam
e
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!examnotes (Martijn@.discussions.microsoft.com) writes:
> I'm making an SQL script that has to drop some columns. The problem is
> that the column has a default value and therefore I can't use the DROP
> COLUMN directly. I've got to drop the 'default' constraint, but the
> problem is that we have to use this script on different database and
> then the constraint name is not always the same.
> (example:)
> DB1 -> DF_COLUMNX_ddf87d67s68
> DB2 -> DF_COLUMNX_ddf79djks90
> I know how to get the Constraint name but I can't use that as a variable
> in the DROP CONSTRAINT function.
> Has someone a solution for this problem? It has to be done with scripting!
SELECT @.default_name = o2.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = @.tbl
AND c.name = @.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You'll have to resort to dynamic SQL:
declare @.constraint varchar(8000), @.str varchar (8000)
set @.constraint = 'DF_COLUMNX_ddf87d67s68'
set @.str = 'alter table MyTable drop constraint ' + @.constraint
exec (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Martijn" <Martijn@.discussions.microsoft.com> wrote in message
news:3C62E773-C60A-46ED-A8A0-32E5AFDC7ACF@.microsoft.com...
Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint
name
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!|||To have more control over constraint names, create constraints using the
ALTER TABLE:
alter table owner.table
add constraint constraint_name
default (<default_value | default_expression> )
for column_name
with values
go
This way you control the names of constraints (in this case the name of the
default constraint).
ML