CODEWITHSUNDEEP
sql-servertriggers
user34537
user34537

Reputation:

How do i recreate a trigger in SQL Server?

i use the statement drop trigger if exist TRIGGER in sqlite but sql server doesnt like the if statement. (i guess exist is the offending word). I do this right next to my create trigger statement because i want to drop older triggers with the same name so i can replace it with this new one.

How do i do this in SQL server?

Upvotes: 3

Views: 3623

Answers (5)

Tobias
Tobias

Reputation: 2139

Since version 2016 this syntax is also supported by Microsoft SQL Server:

DROP TRIGGER IF EXISTS trigger_name

Upvotes: 0

Kip
Kip

Reputation: 109423

I find this to be a more compact SQL Server equivalent to MySQL's DROP TRIGGER IF EXISTS syntax:

IF OBJECT_ID('XXXX', 'TR') IS NOT NULL
    DROP TRIGGER XXXX

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40359

I'd use something like:

IF objectproperty(object_id('dbo.xxx'), 'isTrigger') = 1
    DROP PROCEDURE dbo.xxx
GO
CREATE TRIGGER dbo.xxx [etc]

replacing xxx with your trigger name (and dbo with the relevant schema, if necessary).

Alternatively, you could just use

ALTER TRIGGER dbo.xxx [etc]

Upvotes: 0

John Sansom
John Sansom

Reputation: 41879

You can check for the existence of a specific Trigger like so.

IF EXISTS
(
select name
from sys.objects
where type='TR' and name ='Trigger Name'
)
BEGIN

--Add your Trigger create code here

END

Upvotes: 2

cometbill
cometbill

Reputation: 1619

in SQL Server Management Studio (and, I think in Query Analyzer) right-click the trigger in the explorer, and choose the Script-as option, choose 'Drop Trigger' in clipboard, and SSMS will create the T-SQL syntax for you to drop that trigger.

Sorry I haven't given you T-SQL you can copy and paste, but this way you'll know how to do it for next time.

Upvotes: 4

Related Questions