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