Tracking DDL(Data Definition Langauage) changes
This may be bit advance for some. Go through DDL Trigger first.DML Trigger http://sunitkanyan.blogspot.in/p/triggers-in-sql-server-trigger-is_89.html
In application development , the database changes are general. Whenever some workflow in application require to change, generally sql developer perform the ddl changes and later on or same time forgot out what commands he/she had run in order to perform task. Sometime developer require to perform a task by adding some columns in table or something like that ,but administrator dn't aware about all change.So using DDL trigger we can track that all.
DDL trigger in sql server , helps a developer/administrator to track the DDL changes and command run.
Suppose I am a sql developer, I had execute
ALTER TABLE table1 Add ColumnNew nvarchar(225)
& that cause a new column in table1. If user wants to tack the sql command ,action, user etc etc the DDL Trigger is best solution in case for all DDL changes.
DDL Trigger: - The trigger used to track and work on ddl changed. DDL >> Data Definition Language
Why require
DDL Trigger:
- To track ddl change.
- For audit trail of data definition change.
- For restricting user to perform data definition changes.
Suppose a user tried to delete a table. In that case trigger can be used to restrict user to delete trigger or the command can be tracked.
Where to record ddl changes:
- Can track /stored in same database.
- Can use different database to track.
- Can use the different server/linked server in track ddl changes
DDL
trigger applicable level:
Database Level: The triggers which are only applicable to database on which that has
been applied.
Location
where Database level triggers can be seen:
Databases
>> [Specific Database] >> Programmability >> Database
triggers
Image shows
the database level ddl trigger for database MYBlog
Location
where for Server lever trigger can be seen:
Database
Engine Instance name (SQL05)>> Sever Objects >> Triggers
Database level ddl trigger:
Suppose
we does not want that application /user can create/alter/drop table . the ddl
trigger can restrict user
Trigger
NoNewTablePlease restrict to perform create table /alter table / drop table
operations
Same way we
can perform number of operations/restriction as such revoking permission /
grant permission on database objects.
Deactivating/Activating the ddl trigger :
Disable trigger NoNewTablePlease on database
It will disable
trigger named NoNewTablePlease,
Disable trigger All on database
Disable all ddl
trigger on a database
Enable ddl
trigger
Enable trigger NoNewTablePlease on database
Enable trigger All on database
Enable all
trigger for a database
To
fetching the data from ddl command & fill in table to track audit trail for
ddl command
EVENTDATA()
function provider detail of ddl command that can be used in dl trigger and it’s
collect parameters in xml format , a programmer can easily get that parameter
& can record as per org/application demands.
It contain
information regarding
- · DatabaseObject
- · DatabaseName
- · EventType
- · TSQLCommand
- · LoginName etc etc
DatabaseObject
:- name of object on which some action/event is performed
DatabaseName
: database on which sql query is running /effecting db
EventType:
the type of action /event performed such as Create_Table
,Alter_Table,Create_Database etc
TSQLCommand:
the command performed/run.
LoginName:
the name of user under which query is being executed.
Perform
audit trail of the all ddl commands /statements run:-
Server Level Trigger:-
Restrict
user to perform server level action as create database , drop database , create
alter login etc etc
You can explore more on your development machine , Go through DDL Trigger as well.
-
Sunit
Sunit
technocrats@sunitkanyan.in
No comments:
Post a Comment