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 PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.
No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>
|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers every
>> few months? If yes, why
>> ?.
>>
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>