February 27, 2009 at 2:16 am
Hi Everybody,
I am using SQL 2005
I had a question regarding instead of triggers. Can we have all 3 triggers on a table like 2 ´normal triggers and 1 instead of trigger
Trigger for Insert
Trigger for Update
Instead of Delete Trigger
Before creating a trigger , I drop the trigger it ,if the trigger exists and re-create it. (so that the script can be run any number of times on the SQL Query Analyser)
ALL THESE TRIGGERS ARE ON THE SAME TABLE. The script is as below. When I run the script for all the 3 triggers in one go, it gives me an error.. How do I solve this ?
------------------------------------------------------------
---CREATE TABLE SCRIPTS ARE BELOW-- TABLES USED IN THE EXAMPLE BELOW
CREATE TABLE TABLE_A(NAME VARCHAR(20))
GO
CREATE TABLE TABLE_A_HISTORY(NAME VARCHAR(20))
GO
---------------SCRIPT FOR TRIGGER STARTS HERE ---------MY QUESTION FOR THE BELOW SCRIPT------------------------------------
----
-- BEGINING OF INSTEAD OF TRIGGER FOR DELETE on TABLE_A-------
IF OBJECT_ID(N'[triggerForDelete_DELETE]') IS NOT NULL
DROP TRIGGER [triggerForDelete_DELETE]
GO
CREATE TRIGGER [triggerForDelete_DELETE]
ON [dbo].[TABLE_A] INSTEAD OF DELETE AS
PRINT 'DO NOTHING HERE.'
----------------------------------------------------------
-- BEGINING OF INSTEAD OF TRIGGER FOR UPDATE on TABLE_A -----------
IF OBJECT_ID(N'triggerForUpdate_UPDATE]') IS NOT NULL
DROP TRIGGER triggerForUpdate_UPDATE
GO
CREATE TRIGGER triggerForUpdate_UPDATE
ON [dbo].[TABLE_A]
FOR UPDATE AS
INSERT [dbo].[TABLE_A_HISTORY]
SELECT * FROM inserted
--
-- BEGINING OF TRIGGER FOR INSERT on TABLE_A -------------------------
---
IF OBJECT_ID(N'[triggerForInsert_INSERT]') IS NOT NULL
DROP TRIGGER [triggerForInsert_INSERT]
GO
CREATE TRIGGER triggerForInsert_INSERT
ON [dbo].[TABLE_A]
FOR INSERT AS
INSERT INTO [dbo].[TABLE_A_HISTORY]
SELECT * FROM inserted
--------------- SCRIPT ENDS HERE ------------------------------------------------------
I get the error message
[highlight=#ffff11]Msg 2714, Level 16, State 2, Procedure triggerForUpdate_UPDATE, Line 13
There is already an object named 'triggerForUpdate_UPDATE' in the database.
Msg 2714, Level 16, State 2, Procedure triggerForInsert_INSERT, Line 4
There is already an object named 'triggerForInsert_INSERT' in the database.[/highlight]
Thanks in Advance,
February 27, 2009 at 4:27 am
I just added GO statements between those scripts for triggers and it started to work again. Here is the working script.
--BEGINING OF INSTEAD OF TRIGGER FOR DELETE on TABLE_A-------
IF OBJECT_ID(N'[triggerForDelete_DELETE]') IS NOT NULL
DROP TRIGGER [triggerForDelete_DELETE]
GO
CREATE TRIGGER [triggerForDelete_DELETE]
ON [dbo].[TABLE_A] INSTEAD OF DELETE AS
PRINT 'DO NOTHING HERE.'
GO
----------------------------------------------------------
-- BEGINING OF INSTEAD OF TRIGGER FOR UPDATE on TABLE_A -----------
IF OBJECT_ID(N'triggerForUpdate_UPDATE') IS NOT NULL
DROP TRIGGER triggerForUpdate_UPDATE
GO
CREATE TRIGGER triggerForUpdate_UPDATE
ON [dbo].[TABLE_A]
FOR UPDATE AS
INSERT [dbo].[TABLE_A_HISTORY]
SELECT * FROM inserted
GO
-- BEGINING OF TRIGGER FOR INSERT on TABLE_A -------------------------
---
IF OBJECT_ID(N'[triggerForInsert_INSERT]') IS NOT NULL
DROP TRIGGER [triggerForInsert_INSERT]
GO
CREATE TRIGGER triggerForInsert_INSERT
ON [dbo].[TABLE_A]
FOR INSERT AS
INSERT INTO [dbo].[TABLE_A_HISTORY]
SELECT * FROM inserted
Go
--------------- SCRIPT ENDS HERE ------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply