Trigger with an event log that need to log attempt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger with an event log that need to log attempt

Author

Topic

Peter81
Starting Member

2 Posts

Posted - 2011-06-29 : 06:57:07

HI
Here Is what i need it to do I need to log the event type from my trigger but i have missing code i can not hard code.

alter TRIGGER trg_prevent_view_drop
ON DATABASE FOR DROP_VIEW

AS
PRINT 'View dropping is prohibited !!!'
PRINT 'Rolling back Transaction'

ROLLBACK TRANSACTION

Declare @attempt_type as char (10)

Missing code

INSERT INTO event_log (username, event_type,event_time, table_name)
select SYSTEM_USER,@attempt_type,GETDATE(),TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

Thanks Peter

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 07:55:09

I guess you just need to set the attempt_type?
What do you want to set it to? Looks like maybe the same value as in your print statement so I don't see why you can't hard code that as well.

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

Peter81
Starting Member

2 Posts

Posted - 2011-06-29 : 08:44:16

THANKS I HAVE IT
alter TRIGGER trg_prevent_view_drop
ON DATABASE FOR DROP_VIEW

AS
declare @event varchar (50)
declare @TABLE_NAME varchar (50)
SET @event= EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)')
SET @TABLE_NAME=EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(50)')
PRINT 'View dropping is prohibited !!!'
PRINT 'Rolling back Transaction'

ROLLBACK TRANSACTION

INSERT INTO event_log (username, event_type,event_time, table_name)
select SYSTEM_USER,@event,GETDATE(),@TABLE_NAME

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources