DDL and Logon Triggers

/*


DDL Triggers to capture events in the database

drop trigger ddltrigger_altertable on database

drop trigger ddltrigger_drop_table on database

drop table dbo.ddl_table_log

drop table dbo.ddltable_test

*/

------------------------------------------------
-- 1. DDL Trigger to capture alter table events
------------------------------------------------
-- create a table to capture alter table events

create table dbo.ddl_table_log
(
eventtime datetime,
eventowner nvarchar(100),
eventtsql nvarchar(4000)
)
go
--------------------------------------------------
/*
create the ddl trigger for alter table
Note; XML is case sesnitive
So /EVENT_INSTANCE/TSQLCommand/CommandText)[1]
should be provided as it is
If you change the case it will not work
*/
create trigger ddltrigger_altertable
on database for alter_table
as
declare @data xml
set @data = EVENTDATA()
insert into dbo.ddl_table_log
values
(
GETDATE(),
CURRENT_USER,
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(4000)')
)
--------------------------------------------------
-- Now, create a table to test alter table command to log
create table dbo.ddltable_test
(
orgid int identity(1,1) primary key ,
orgname varchar(20) not null
)
go

-- Insert a record
insert into dbo.ddltable_test(orgname)
values ('DivaInfotech')
go

select * from dbo.ddltable_test
go
/*
Now there is a new organization
which requires column width to be increased
increase orgname from varchar(20) to varchar(80)
*/
alter table dbo.ddltable_test
alter column orgname varchar(80) not null
go

-- check alter table commad is logged into ddl_table_log
select * from dbo.ddl_table_log
go

-- insert orgname having value greater than 20
insert into dbo.ddltable_test(orgname)
values ('Indian Oil and Natural Gas Corporation Limited')
go
-----------------------------------------------
/*
In a business enviornment, it may be required
that even system administrator should not drop
or truncate tables by accident.
*/
------------------------------------------------
-- 2. DDL Trigger to prevent drop table
------------------------------------------------
create trigger ddltrigger_drop_table
on database for drop_table
as
print ' You can not drop and truncate the table'
rollback
------------------------------------------------
-- Let us try to drop the table
drop table dbo.ddl_table_log
go
------------------------------------------------

/*
Logon triggers

Logon triggers can be used to record information
of the login user or prevent a particular user
to logon during business hours
*/

create trigger checklogontrigger
on all server for logon
as

declare @currenttime datetime = getdate()
if ORIGINAL_LOGIN() = 'maintuser'
and @currenttime between '09:00' and '18:00'
begin
   print ( 'Maintenance users can not login in business hours')
   rollback
end

-----------------------------------------------