script to create multiple triggers

[pvmurphy]

I would like to create an SQL script that will create triggers on 25 different tables(is it possible?)within a database, the trigger will always be the same except for of course the table name. I tried creating a script using a Cursor (it will be use very infrequently!) but get an error at the Create Trigger statement. If what I’m trying is allowed, can you help me correct my script?

Thanks,
Jim

USE testbase
DECLARE pubnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '___Cat'
OPEN pubnames_cursor
DECLARE @tablename sysname

FETCH NEXT FROM pubnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
---EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
CREATE TRIGGER tgInsertTest
ON dbo. + @tablename
FOR INSERT
AS
BEGIN
RAISERROR ('just a test',0,1)
END

--- + @tablename )
---PRINT @tablename
END
FETCH NEXT FROM pubnames_cursor INTO @tablename
END
CLOSE pubnames_cursor
DEALLOCATE pubnames_cursor

 

[JayKusch]

What error message are you receiving?

Thanks

J. Kusch

 

[pvmurphy]

I receive the follow message when I cehck my syntax.

Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'TRIGGER'.

Thanks,

Jim

 

[rsinj]

instead of :

CREATE TRIGGER tgInsertTest
ON dbo. + @tablename
FOR INSERT

try using EXEC or sp_executesql. you cannot do it the way you have it (as you can see).

I am quite certain it wouldn't like dbo. + @tablename - this needs to be evaluated and you can do that in building the SQL string you use with EXEC/sp_executesql.

To verify, just go into isql and do it once manually so see if it would work.

CREATE TRIGGER tgInsertTest
ON dbo. + "TempTrigger"
FOR INSERT

In any case, I've found that in a stored proc, you almost always are better off performing DDL statements through EXEC/sp_executesql as opposed to in the sp directly as you are trying.

 

[nigelrivett]

create's have to be the first statement in batches so you will need to do this in dynamic sql.
I usually do it by generating the text and copying the result to another windw and executing it - in that way you can look at the scripts and save them.
If it's needed to be generated and executed then I will create a file and execute using osql so that the file can be saved as a record.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.