SQL2000 create trigger within stored procedure

[abraxas]

Hello,
Was wondering if you could help me with a problem.
I wish to create a stored procedure which creates a trigger and a stored procedure that drops a trigger. I realise I can incorporate both into the one procedure but I cannot get past the first step of creating a trigger.

Within Enterprise Manager I get the following message

Error 156: Incorrect syntax near keyword TRIGGER

when I hit Check Syntax button
Here is the Create procedure

Create procedure [dbo].[sp_CreateTrigger] As

Create Trigger [trg_myTrig] On [dbo].[myTable]
After Insert, Update
As
Print 'Hello World'

Return

I was wondering if I have to encapsulate the trigger statement with something? I eventually pruned the trigger contents down to the simple print statement with no success. I have dug around quite a bit but have found no similar problems or examples for that matter. Can someone offer some light on this?

Thank you in advance

Anthony

Thank you in advance

 

[SQLDenis]

Trigger Limitations
CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

workaround below

create table myTable (id int)
go
create procedure [dbo].[sp_CreateTrigger] As

declare @sql varchar(500)
select @sql ='
Create Trigger [trg_myTrig] On [dbo].[myTable]
After Insert, Update
As
Print ''Hello World''

'


exec ( @SQL)

Return


go

test it out

sp_helptext trg_myTrig

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> I wish to create a stored procedure which creates a trigger and a stored procedure that drops a trigger.

Argh. Trouble.

Can you create trigger, then enable/disable it when necessary instead?


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[abraxas]

Hello again, thank you for the quick response!
I'll give SqlDennis's very interesting suggestion a go if all else fails. The reason being I tried VonGrunt's solution which is actually what I want to implement.

New Proc

Creat Proc dbo.sp_DisableTrig As
Alter Table dbo.MyTable Disable Trigger trg_myTrig

return

I got the trigger to captured Updated data (from inserted) to another table (MyOtherTable) to see how it was firing. Disable Trigger worked great (it didn't capture)

Opened up QA ran
select on one record in MyOtherTable
Update on that record in MyTable -- Triggered
select on one record in MyOtherTable -- yep, changed
Execute sp_DisableTrig -- 1 rec affected
/* tried this as well */ Alter Table dbo.MyTable Disable Trigger trg_myTrig /* or All */
Update on that record in MyTable -- didn't trigger!
select on one record in MyOtherTable -- not changed

great... nothing changed disable worked
..., but when I re-enabled it.
I found I had to Enable Trigger before each Update to force it to trigger thus...

Alter Table dbo.MyTable Enable Trigger trg_myTrig
select on one record in MyOtherTable
Update on that record in MyTable -triggered
select on one record in MyOtherTable -- changed data

Alter Table dbo.MyTable Enable Trigger trg_myTrig -- Why?
Update on that record in MyTable -- this would not trigger if above statement was not there. I used different set values
select on one record in MyOtherTable

What does Disable/Enable Trigger do in regards to subsequent Updates?

Once again thanks for your help
Anthony

 

[abraxas]

Back again after tasting victory thanks to SQLDenis and his creative workaround. The actual trigger text is quite lengthy and I had not seen so much red text for a long while! But it worked!!!

The one proc checks for existence of trigger and drops if found or creates if not found. The Updating has returned to normal as opposed to what ever Trigger Disable was doing. See above Post. I still would like to know what it was doing to need Trigger Enable before each update statement. Strange.

Thank you very much to you both, this place is wonderful!

Kind Regards