dropping a trigger

Is there any precautions needed to be made when dropping
u/i/d triggers on table involved in replication.
Thanks,
DonaldDonald,
I assume you mean these are your own triggers, and not the triggers used in
Merge replication? Don't drop any replication triggers!
If these are user-defined triggers, they will not be replicated anyway, so
there should be no harm in dropping them. However, you might consider
disabling them first, before dropping, just to make sure everything works.
Do this on a test system first.
Ron
--
Ron Talmage
SQL Server MVP
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:2e5e01c49fda$452d7370$a501280a@.phx.gbl...
> Is there any precautions needed to be made when dropping
> u/i/d triggers on table involved in replication.
> Thanks,
> Donald

dropping a trigger

Is there any precautions needed to be made when dropping
u/i/d triggers on table involved in replication.
Thanks,
Donald
Donald,
I assume you mean these are your own triggers, and not the triggers used in
Merge replication? Don't drop any replication triggers!
If these are user-defined triggers, they will not be replicated anyway, so
there should be no harm in dropping them. However, you might consider
disabling them first, before dropping, just to make sure everything works.
Do this on a test system first.
Ron
Ron Talmage
SQL Server MVP
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:2e5e01c49fda$452d7370$a501280a@.phx.gbl...
> Is there any precautions needed to be made when dropping
> u/i/d triggers on table involved in replication.
> Thanks,
> Donald
sql

Drop trigger with a Variable Trigger Name

Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.

The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger

I have the following Trigger

the error is at

DROP TRIGGER @.DeleteTrigger

I'm guessing it dosen't like the trigger name being a variable instead of a static name

how do i get around this?

thanks in advance

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @.DeleteTrigger as nvarchar(max)

select @.DeleteTrigger = TableName FROM DELETED

IF OBJECT_ID (@.DeleteTrigger,'TR') IS NOT NULL

DROP TRIGGER @.DeleteTrigger

GO

END

GO

You couldn't use

DROP TRIGGER @.DeleteTrigger

But you could use Dynamic SQL:

Code Snippet

DECLARE @.Query varchar(100)

SET @.Query = 'DROP TRIGGER '+@.DeleteTrigger

EXECUTE(@.Query)

But you must avoid SQL Injection in with code.

|||

hiya, thanks for getting abck so quickly...

I've changed the trigger to be...

Declare @.DeleteTrigger as nvarchar(max)

select @.DeleteTrigger = TriggerName FROM DELETED

IF OBJECT_ID (@.DeleteTrigger,'TR') IS NOT NULL

DECLARE @.Query varchar(100)

SET @.Query = 'DROP TRIGGER '+@.DeleteTrigger

EXECUTE(@.Query)

GO

but when I delete from my application i get this error

Incorrect syntax near '\'.

very usefull..... any idea's?

|||

Very strange. You code works on my system

Try to add

PRINT @.Query

before EXECUTE may be something wrong with data in DELETED table

|||

found the error i think, but not how to overcom it...

the printshowed this

DROP TRIGGER KAVBRACK\daveh14

and KAVBRACK\daveh14 is the correct name of the trigger, including the slash

do i need to put it in single quotes? or is there another way?

|||

Use:

Code Snippet

SET @.Query = 'DROP TRIGGER ['+@.DeleteTrigger+']'

|||

got it, it's squared brackets, everythings working fine now

thanks for your help :-)

Drop Trigger from Replicated Table?

I created a trigger on a replicated table in a publishing database on SQL Server 2000. When I attempt to ALTER TABLE ... DISABLE TRIGGER ..., I get a message that I cannot alter the table since it is part of a publication. Does anyone know if I would be able to issue a DROP TRIGGER or ALTER TRIGGER on a replicated table?

Thanks,

Gerald

you can not Disable trigger by alter statment in a replicated table. Drop the trigger is the only way...

Madhu

sql

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 and create procedure

I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...

ThanksWhat is the trigger for?

There's no way I know of to disable the trigger...but what's the big deal with

DROP TRIGGER

EXEC Sproc

CREATE TRIGGER

The only thing is, whatever the trigger is for, it's there for a reason, and wouldn't dropping cause you any data integrity issues?|||Actually, it can be done in SQL 2000. I have never tried it, but there is ALTER TABLE syntax for enabling/disabling a trigger.

As Brett pointed out, though, you want to be sure that not only your process but any other process accessing the table will not be adversely affected by the sudden disabling of the trigger.|||No Sheet!

Still, once I've put a trigger in place, I've never had a need (or want) to remove it.

You might want to consider some alternatives

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT
AS
BEGIN
UPDATE t SET t.Col2 = t.Col2 * 2
FROM myTable99 t JOIN inserted i ON t.Col1 = i.Col1
END
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
DISABLE TRIGGER myTrigger99
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
ENABLE TRIGGER myTrigger99
GO
INSERT INTO myTable99(Col2) SELECT 2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO