"create trigger" inside "create proc" does not work. Why?
-
This code works fine:
--CREATE PROCEDURE #t as
create trigger [Anamoly Descriptions_boolean] on [Anamoly Descriptions] for insert, update as
if update([Custom]) begin
update [Anamoly Descriptions] set [Custom]=-1
where not [Custom]in(0,-1,null)
and patternID in (select patternID from inserted)
end
GO
But when I uncomment the first line the parser returns this error:
Server: Msg 156, Level 15, State 1, Procedure #t, Line 2
Incorrect syntax near the keyword 'trigger'
What is going on? Please help.
--Regards,
Igor
-
AFAIK, you can't issue Create Trigger inside a stored proc. BOL: "CREATE TRIGGER must be the first statement in the batch and can apply to only one table."
Can I ask why you'd want to do that anyway? Don't mean to be rude, but it's a little unorthodox.
-
Igor - what exactly are you trying to do ?!
Your trigger is set on the table "Anamoly Descriptions_boolean" - and will be fired whenever there is an insert/update on this table.
You can use your stored procedure to update or insert into this table and this will automatically activate the trigger!!!
**ASCII stupid question, get a stupid ANSI !!!** -
What I am trying to do is this.
I want to have a script that re-creates the entire database schema (80 tables) for MSDE distribution. Where do I store this script (generated by SQL Server)? Wouldn't it be nice to store it as INIT_DB stored procedure (rather than in VSS)? [Yes, I understand that I cannot run it from an empty database and that I have to copy it manually].
The two circumstances exacerbate my situation:
1) Generated script (~10,000 lines) has syntax errors: it in some cases does not enclose object names into brackets - definitely a bug, - so I have to manually fix this.
2) Copying database schema (only!) is inhibited by many extraneous (for our customers) objects that replication technology creates.
Thus I wanted to generate 10,000 lines script and save it as a set of stored procedures (DROP_TABLES, CREATE_TABLES etc.) with the master one:
CREATE PROC INIT_DB as
exec dropFKs
exec dropTriggers
...
exec createTriggers
go
Thanks for response. I guess I did not realize well enough what a "batch" is in this context. The limitation that you, Merrill, mentioned, and I was well aware of, essentially means that TSQL does not support Create <VIEW / TRIGGER / PROC> inside a stored procedure. Is there a good reason for this? Does for instance Oracle allow doing this? Just curious.
Regards,
--Igor
Unorthodox? Agreed. But this is what we have to live with during our MS Access (you may smell its spirit from the above) to MS SQL transition
-
I don't think it's possible to "stretch" stored procs to do what you are attempting. If it were me, I'd head toward just troubleshooting the script instead ... then run it from the command line to create the database.
-
Choices are limited if not none.
I certainly do not want to do something like this:
create proc INIT_DB as
...
create table...
create table...
...
exec
(' create trigger ...
go
create trigger ...
go
...
/* under 4000 characters chunk */
'
+
' /* next chunk*/
Create trigger...
go
Create trigger
go
')
...
go
...Or do I?
-
...Or do I?....
No, you don't!
But if you must be unorthodox my 2 cents would be "the proof of the pudding is in the eating..." - why don't you experiment with small chunks (a couple of tables and triggers) at a time and test, test, test....
**ASCII stupid question, get a stupid ANSI !!!** -
A few search & replace (RegEx is on) in the stubborn to-be-SP text:
Search '
Replace ''
Search: create trigger
Replace: exec ('create trigger
Search: SET QUOTED_IDENTIFIER OFF
Replace: ')\nSET QUOTED_IDENTIFIER OFF
did he trick!
-
Igor - if you get it working could you pl. post the script for at least one of the procedures (triggers, tables...anything...) - I'll store it in my unorthodox folder which is currently very slim...
**ASCII stupid question, get a stupid ANSI !!!** -
The following script is an excerpt from 2000+ lines Stored Procedure
ALTER PROCEDURE dbo.scriptCreateTriggers
as
SET QUOTED_IDENTIFIER ON
--
SET ANSI_NULLS ON
--
exec ('create trigger [Anamoly Descriptions_boolean] on [Anamoly Descriptions] for insert, update as
if update([Custom]) begin
update [Anamoly Descriptions] set [Custom]=-1 where not [Custom]in(0,-1,null) and patternID in (select patternID from inserted)
end
--
')
SET QUOTED_IDENTIFIER OFF
--
SET ANSI_NULLS ON
--
SET QUOTED_IDENTIFIER ON
--
SET ANSI_NULLS ON
--
exec ('create trigger [trgOnUIAnamoly Descriptions] on [Anamoly Descriptions] for update, insert as exec emptyRecycleBin
--
')
SET QUOTED_IDENTIFIER OFF
--
SET ANSI_NULLS ON
--
SET QUOTED_IDENTIFIER ON
--
SET ANSI_NULLS ON
--
-
thx. threw it into the vault and turned the key! will try out when I'm looking for a fun-time break!
**ASCII stupid question, get a stupid ANSI !!!** -
Hi,
not to be too pedantic, but it seems to me that the real problem is to explain to Igor that you can build all those 'CREATE something' scripts, put each on text files then run each with OSQL (batch command line version of TSQL), so yo can have a single '.bat' file, with all those 'OSQL...' statement and a lot of small files, one for tables, one for triggers, and so on...
(I came also from an Access to Sql road map...)
just my 2 cent... HTH
-
As I mentioned in my post of 7/29/2005 12:23:00 PM I am trying to avoid creating a new subject to maintenance like a set of text .sql files (let SQL backup do it).
-
luigi - i was going to tell you that igor already knew this but i see that he's here to speak for himself...
**ASCII stupid question, get a stupid ANSI !!!** -
Sorry, maybe I did'nt read very well... but for me it's around 10PM... still working (or better said learning from people on this forum...)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply