Tracking DDL(Data Definition Langauage) changesThis 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.