September 10, 2010 at 9:36 am
I have been googling this one for much of the afternoon with no success...
Is it possible to add a trigger to a database only if a condition is true? If so I'm struggling to get the syntax right.
In my development environment database I have a table, and I have created a trigger on it that works. I am now attempting to create a script which can be ran against other databases belonging to our live clients.
The thing is in our clients databases, this table MAY have been replaced with a view. What I'm after is to try and create a single script that will run the "create trigger" statements IF it is a table, but do nothing if it is a view. Is this possible? I'm just getting errors in SQL2k8 MS "Incorrect syntax near the keyword 'TRIGGER'." But the trigger works on its own, its just when I script it as create and attempt to include this check logic that it goes wrong?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tablename]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
<Create trigger logic, from MSSQL's script as create>
end
any idea how I get this to work?
September 10, 2010 at 9:43 am
You could use dynamic sql:
IF someCondition
BEGIN
EXEC('CREATE TRIGGER ....')
END
-- Gianluca Sartori
September 10, 2010 at 9:46 am
something like
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableNameHere]') AND type in (N'U'))
begin
you create trigger script here
end
you might also want to add some logic to check if the trigger exists already. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2010 at 9:48 am
dur!!! I guess you pretty much had the same thing i did. Not sure why that wouldn't work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2010 at 9:51 am
because of the error "A triggermust be the first command in a batch"
you have to use dynamic SQL after the IF EXISTS test.
that goes the same for procedure/ function/ view for that matter;
Lowell
September 10, 2010 at 9:51 am
SeanLange (9/10/2010)
Not sure why that wouldn't work.
Because some statements have to be come first in a batch. Create trigger is one of them.
-- Gianluca Sartori
September 10, 2010 at 9:52 am
Lowell (9/10/2010)
because of the error "A triggermust be the first command in a batch"you have to use dynamic SQL after the IF EXISTS test.
that goes the same for procedure/ function/ view for that matter;
Sorry for echoing, Lowell. We must have pressed the "submit" button in the same exact instant...
-- Gianluca Sartori
September 10, 2010 at 9:52 am
ahh yes. I think I need more coffee. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2010 at 9:52 am
I have essentially this, but I get error "Incorrect syntax near the keyword 'TRIGGER'." which points to the create trigger line. I get what you said above, any way around this, or to surpress the error message that will be the result of trying to create an update trigger against a view?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
begin
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TriggerName]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[AddNewCashSchedules]
CREATE TRIGGER [dbo].[TriggerName]
ON [dbo].[TableName]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
<unimportant trigger logic here>
END
END
GO
September 10, 2010 at 10:14 am
This should work:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TriggerName]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
EXEC('DROP TRIGGER [dbo].[AddNewCashSchedules]')
EXEC('
CREATE TRIGGER [dbo].[TriggerName]
ON [dbo].[TableName]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
--<unimportant trigger logic here>
END
')
END
END
GO
-- Gianluca Sartori
September 10, 2010 at 10:20 am
Works like a charm, thank you very much 😀
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply