title: How to add "IF NOT EXISTS" to create trigger statement
body: <p>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):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How do I conditionally create only if the trigger does not yet exist?  What am I doing wrong here?  StackOverflow has good examples of <code>IF NOT EXISTS</code>, but I can't get this to work in conjunction with a <code>CREATE</code>.  Here is one of my failed efforts:</p>
  
  <pre><code>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
  </code></pre>