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 :-)