Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to add "IF NOT EXISTS" to create trigger statement

I am using sql server 2008 R2. More specifically, Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor). I am new to sql server and procedures/triggers. I have the following code to create a trigger (it works):

CREATE TRIGGER [dbo].[Insert_WithdrawalCodes]     ON  [dbo].[PupilWithdrawalReason]     AFTER INSERT AS  BEGIN     SET NOCOUNT ON;         UPDATE [dbo].[PupilWithdrawalReason] SET DateCreated=dbo.SYSTEMTIME()          WHERE WithdrawalCodeID IN (SELECT WithdrawalCodeID FROM inserted) END 

How do I conditionally create only if the trigger does not yet exist? What am I doing wrong here? StackOverflow has good examples of IF NOT EXISTS, but I can't get this to work in conjunction with a CREATE. Here is one of my failed efforts:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'Insert_WithdrawalCodes')    exec('CREATE TRIGGER [dbo].[Insert_WithdrawalCodes] ON  [dbo].[PupilWithdrawalReason] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE [dbo].[PupilWithdrawalReason] SET DateCreated=dbo.SYSTEMTIME() WHERE WithdrawalCodeID IN (SELECT WithdrawalCodeID FROM inserted) END') GO 
like image 538
thebiggestlebowski Avatar asked Jan 29 '15 21:01

thebiggestlebowski


People also ask

Can only use if update within a create trigger statement?

As mentioned earlier, the UPDATE() function can only be used within a trigger. If the purpose of using the UPDATE() function is to determine if any rows were updated in a table after an UPDATE statement, then the @@ROWCOUNT function can be used instead.

How do you know if a trigger exists?

We can use the sys. triggers catalog view to check the existence of a Database scoped triggers. DML triggers are Database scoped triggers, where as DDL triggers can be DATABASE scoped or SERVER scoped.

Which statement is not allowed in case of triggers?

Since triggers execute as part of a transaction, the following statements are not allowed in a trigger: All create commands, including create database, create table, create index, create procedure, create default, create rule, create trigger, and create view. All drop commands. alter table and alter database.


2 Answers

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIGGERNAME]')) DROP TRIGGER [dbo].[TRIGGERNAME] go IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TABLENAME]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE   TRIGGER [dbo].[TRIGGERNAME] ON [dbo].[TABLENAME] FOR INSERT, UPDATE   AS ...  END 

Based on your updated question... try this:

IF NOT EXISTS (select * from sys.objects where type = 'TR' and name = 'Insert_WithdrawalCodes') EXEC dbo.sp_executesql @statement = N'  CREATE TRIGGER [dbo].[Insert_WithdrawalCodes]     ON  [dbo].[PupilWithdrawalReason]     AFTER INSERT AS  BEGIN     SET NOCOUNT ON;         UPDATE [dbo].[PupilWithdrawalReason] SET DateCreated=dbo.SYSTEMTIME()          WHERE WithdrawalCodeID IN (SELECT WithdrawalCodeID FROM inserted) END    ' 
like image 102
JStevens Avatar answered Sep 16 '22 12:09

JStevens


The best way is to check for objects and drop them if they exist before you create them.

Rather then not creating it at all if it exists, I would approach it the other way, drop it if exists and then create.

Normally in long lenghty scripts if you want to update the definition of a trigger you would just simply add this at the end of that script and your trigger definition will be updated.

So the approach should be create the object but drop it if it already exists rather then dont create it at all if it already exists

IF OBJECT_ID ('[Insert_WithdrawalCodes] ', 'TR') IS NOT NULL    DROP TRIGGER [Insert_WithdrawalCodes]; GO  CREATE TRIGGER ....... 
like image 33
M.Ali Avatar answered Sep 17 '22 12:09

M.Ali