I would like to thanks our readers, sponsors and My wife and son for one more year with in a time of change.I see that our visitors are growing on a daily basis for me it's really a pleasure.
To improve I will need some feedback, only 2 questions !!!
Database Triggers
One more post about SQL Tips and Tricks that i think that have is own value. Today I write about Database Triggers, they are used for security reasons and audit operations, let's see the example.Use Example_DB
go
Create TRIGGER [DBTRG_Test_Drop_Create]
ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS
AS
DECLARE @data xml
DECLARE @cmd nvarchar(350)
DECLARE @logMsg nvarchar(400)
SET @data = eventdata()
SET @cmd = @data.value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(350)')
SET @logMsg = @cmd + ' (' + SYSTEM_USER + ' on ' + HOST_NAME() + ')'
RAISERROR (@logMsg, 10, 1) WITH LOG
-- To visualize the command uncomment the line bellow
-- select @cmd
-- Here we don't allow command that begin with "Create"
-- if the condition is true we do rollback
if left(@cmd,6)='Create'
rollback
-- Here we don't allow command that begin with "Drop"
-- if the condition is true we do rollback
if left(@cmd,4)='drop'
rollback
Note: We can create a table with type specified in variables and insert the attemps of create and drop objects.
Related Posts:
