Trigger

Question

Trigger

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Tara-1044200 SSCoach Points: 15951 More actions · Answer 1

I am getting the following error when creating FOR ALL SERVER

Msg 1098, Level 15, State 1, Procedure [TR_DB_NO_DROPPING_OBJECTS] , Line 58
The specified event type(s) is/are not valid on the specified target object.

Lowell SSC Guru Points: 323546 More actions · Answer 2

syntax...you can fix that....the original trigger i wrote was tested, so it's gotta be a modification.

note i did test on a 2008 server...there may be a difference if this is 2005.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

it does not seem to be systax error because it worked on DATABASE but not working ON SERVER

drew.allen SSC Guru Points: 77014 More actions · Answer 4

I check BOL and it looks like DROP_TABLE is not valid for server level events. You can only use the following at the server level: ALTER_AUTHORIZATION_SERVER, CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE, CREATE_ENDPOINT, ALTER_ENDPOINT, DROP_ENDPOINT, CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER, DENY_SERVER, and REVOKE_SERVER. These make sense, because they're changes that affect the server as a whole. Dropping a table only affects a database, so it makes sense to limit it to DATABASE level triggers.

This means that you'll need to add a DDL trigger to every database where you want to have this trigger in effect. You can use a SERVER trigger on CREATE_DATABASE or ALTER_DATABASE to automatically create/recreate the trigger on the database.

You can find the complete list of DDL events at http://msdn.microsoft.com/en-us/library/ms189871(SQL.90).aspx

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

Hmm thats a pain if i have to add trigger to every database there are more than 400 db's. any trick?

drew.allen SSC Guru Points: 77014 More actions · Answer 6

Tara-1044200 (9/10/2010)
Hmm thats a pain if i have to add trigger to every database there are more than 400 db's. any trick?

Dynamic SQL. That's how I created triggers on a bunch of tables. The same principles can be used for databases.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

If i am creating a trigger for each database, is it possbile to create the same trigger which also includes

prohibit following permissions

DROP TABLE,ALTER TABLE,INSERT,UPDATE,DELETE.

if so how woould i do in the same trigger.

something like this...

CREATE TRIGGER [PRohibit_for_OBJECTS]

ON DATABASE

FOR

ALTER_TABLE,DROP_TABLE,INSERT_TABLE,UPDATE_TABLE,DELETE_TABLE

AS

BEGIN

SET NOCOUNT ON

Tara-1044200 SSCoach Points: 15951 More actions · Answer 8

I dont think i could create the above trigger either using dynamic sql (sp_executesql) or sp_msforeachdb, any help ?

drew.allen SSC Guru Points: 77014 More actions · Answer 9

It's actually fairly easy, because your trigger code doesn't change between databases. Since this is probably only a one-time thing, I would just copy the results of the dynamic SQL generation into a new query window, instead of trying to use sp_executesql. That way you don't need to worry about getting the syntax exactly right or worry about using a cursor.

SELECT 'USE ' + [name] + Char(10)

, 'GO' + Char(10)

, 'CREATE TRIGGER YourTrigger' + Char(10)

, 'ON DATABASE' + Char(10)

, <rest of your trigger code as a string here> + Char(10)

, 'GO' + Char(10)

FROM sys.databases

WHERE [name] NOT IN ('master', 'msdb', .... ) -- list any databases to exclude here.

When outputting text results, SQL will truncate the columns larger than a given length, so you will either need to make sure that none of your columns is longer than that by breaking up your code into multiple columns or by changing the default setting for the length. XML columns have a much larger limit, but then you have to worry about certain characters (like &) getting escaped.

Drew

NOTE: I've used Char(10) (linefeed) as the whitespace character, because it isn't escaped when output as XML whereas the carriage return (Char(13)) is. Both are escaped if XML is output as text.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Tara-1044200 SSCoach Points: 15951 More actions · Answer 10

I get this error

'ALTER TRIGGER' must be the first statement in a query batch.

drew.allen SSC Guru Points: 77014 More actions · Answer 11

You must not have included the "GO" statement in your dynamic SQL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Tara-1044200 SSCoach Points: 15951 More actions · Answer 12

NO i havent but still the error, i thi it probably because of the " USE database"

Tara-1044200 SSCoach Points: 15951 More actions · Answer 13

I am still getting the erorr , may be i cant use this in a single query batch

USE DB1;ALTER TRIGGER.......

USE DB2;ALTER TRIGGER.......

USE DB3;ALTER TRIGGER.......

USE DB4;ALTER TRIGGER.......

'ALTER TRIGGER' must be the first statement in a query batch.

drew.allen SSC Guru Points: 77014 More actions · Answer 14

Tara-1044200 (9/15/2010)
I am still getting the erorr , may be i cant use this in a single query batch
USE DB1;ALTER TRIGGER.......
USE DB2;ALTER TRIGGER.......
USE DB3;ALTER TRIGGER.......
USE DB4;ALTER TRIGGER.......
'ALTER TRIGGER' must be the first statement in a query batch.

As I said earlier, you are missing the "GO" statements.

USE DB1; [font="Arial Black"]GO[/font] ALTER TRIGGER.....

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Tara-1044200 SSCoach Points: 15951 More actions · Answer 15

Tara-1044200

SSCoach

Points: 15951

September 15, 2010 at 11:43 am

#1221567

still same problem