ddl trigger example
ddl trigger example in sql server
ddl triggers can be used to
In this video we will discuss DDL Triggers in sql server.
In SQL Server there are 4 types of triggers
1. DML Triggers - Data Manipulation Language. Discussed in Parts 43 to 47 of SQL Server Tutorial.
2. DDL Triggers - Data Definition Language
3. CLR triggers - Common Language Runtime
4. Logon triggers
What are DDL triggers
DDL triggers fire in response to DDL events - CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure etc...). For the list of all DDL events please visit https://msdn.microsoft.com/en-us/library/bb522542.aspx
Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. Example - sp_rename system stored procedure
What is the use of DDL triggers
If you want to execute some code in response to a specific DDL event
To prevent certain changes to your database schema
Audit the changes that the users are making to the database structure
Syntax for creating DDL trigger
CREATE TRIGGER [Trigger_Name]
ON [Scope (Server|Database)]
FOR [EventType1, EventType2, EventType3, ...],
-- Trigger Body
DDL triggers scope : DDL triggers can be created in a specific database or at the server level.
The following trigger will fire in response to CREATE_TABLE DDL event.
CREATE TRIGGER trMyFirstTrigger
Print 'New table created'
To check if the trigger has been created
1. In the Object Explorer window, expand the SampleDB database by clicking on the plus symbol.
2. Expand Programmability folder
3. Expand Database Triggers folder
Please note : If you can't find the trigger that you just created, make sure to refresh the Database Triggers folder.
When you execute the following code to create the table, the trigger will automatically fire and will print the message - New table created
Create Table Test (Id int)
The above trigger will be fired only for one DDL event CREATE_TABLE. If you want this trigger to be fired for multiple events, for example when you alter or drop a table, then separate the events using a comma as shown below.
ALTER TRIGGER trMyFirstTrigger
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
Print 'A table has just been created, modified or deleted'
Now if you create, alter or drop a table, the trigger will fire automatically and you will get the message - A table has just been created, modified or deleted.
The 2 DDL triggers above execute some code in response to DDL events (CREATE_TABLE, ALTER_TABLE, DROP_TABLE).
Now let us look at an example of how to prevent users from creating, altering or dropping tables. To do this modify the trigger as shown below.
ALTER TRIGGER trMyFirstTrigger
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
Print 'You cannot create, alter or drop a table'
To be able to create, alter or drop a table, you either have to disable or delete the trigger.
To disable trigger
1. Right click on the trigger in object explorer and select "Disable" from the context menu
2. You can also disable the trigger using the following T-SQL command
DISABLE TRIGGER trMyFirstTrigger ON DATABASE
To enable trigger
1. Right click on the trigger in object explorer and select "Enable" from the context menu
2. You can also enable the trigger using the following T-SQL command
ENABLE TRIGGER trMyFirstTrigger ON DATABASE
To drop trigger
1. Right click on the trigger in object explorer and select "Delete" from the context menu
2. You can also drop the trigger using the following T-SQL command
DROP TRIGGER trMyFirstTrigger ON DATABASE
Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. The following trigger will be fired when ever you rename a database object using sp_rename system stored procedure.
CREATE TRIGGER trRenameTable
Print 'You just renamed something'
The following code changes the name of the TestTable to NewTestTable. When this code is executed, it will fire the trigger trRenameTable
sp_rename 'TestTable', 'NewTestTable'
The following code changes the name of the Id column in NewTestTable to NewId. When this code is executed, it will fire the trigger trRenameTable
sp_rename 'NewTestTable.Id' , 'NewId', 'column'
Text version of the video
All SQL Server Text Articles
All SQL Server Slides
All Dot Net and SQL Server Tutorials in English
All Dot Net and SQL Server Tutorials in Arabic
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
If you need DVDs or to download all the videos for offline viewing please visit
Slides and Text Version of the videos can be found on my blog
Tips to effectively use my youtube channel.
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
You are such an excellent tutor!!!! I want to ask you a favor. I am attending your C# Tutorial, MVC, ASP .I want to attend all the lists that you gave ,but I also want the slides that you explain from. Please upload them for us.Thanks My teacher
For towns, each building is described, along with what and who can you can talk to, who to buy skills from, and what quests are available. For the outlying areas, the dungeons are listed.
Dungeon maps are not given -- they would be too extensive to fit easily into a web page and the automapping in the game is excellent. Also, every dungeon should be explored completely to get all of the loot, but only puzzles and hidden locations are described. I also skip most of the fighting because it isnt something that you can easily describe, nor does it matter in most places, except that you have to survive it. I do list the creatures that you will encounter in a dungeon or grid location to give you an idea of how difficult the location is.
Stores are listed with a "buy" and "sell". The "buy" value is multiplied by the items value to determine the price you have to pay for it. The "sell" value is divided by the items value to determine the price you can sell it to the store for. Higher is always worse, and a "buy" or "sell" of 1 means that you are buying/selling an item at cost.
Every location has a "reset" timer. This starts when you first enter the area, and after it "goes off", the entire grid square resets: monsters reappear and random treasure is replaced. Nonrandom treasure (including most stat-gaining liquids) is not replaced. All dungeons have a reset of 2 years (24 months), unless otherwise noted. Overland areas have reset times listed with their descriptions.
Artifacts are unique items that can be found. They come in two flavors: Minor artifacts are always benificial and have a value of 20000gp. Major artifacts always have a drawback, but their benificial powers are much stronger. They have a value of 30000gp. There are 15 minor and 15 major artifacts -- some of these artifacts are placed at specific locations; others are randomly generated.
Table of Contents.