In the SQL server, there are four types of triggers. First, we have the DML triggers. DML stands for Data Manipulation Language. We discuss these triggers in the below post.
- DML triggers ,After Insert,After Delete Trigger with examples
- After Update Trigger in Sql Server With Example
- Instead Of Insert Triggers in Sql server With Example
- Instead Of Update Trigger In Sql Server With Example
And then we have DDL triggers, DDL stands for Data Definition Language. In this post, we’ll discuss DDL triggers, and then we have CLR triggers, CLR stands for Common Language Runtime. And finally, Log-On triggers.
- DML triggers
- CLR triggers
- Log-On triggers
What are DDL triggers?
DDL triggers are executed in response to DDL events. So the immediate obvious question that comes to our mind is what are DDL events and when are these events raised?
whenever you create, alter, or drop a database object, then a corresponding DDL event is raised. For example, when you create a table using the create table DDL statement, the associated event is create_table. So that event is raised.
And if you have a trigger associated with that event, then when you create a table automatically that associated trigger will be fired.
Similarly, when you drop a stored procedure, the drop_procedure event is raised. When you create a function, create_function DDL event is raised.
For the full list of DDL events, please visit MSDN link- MSDN ddl events
So whenever you execute DML statements associated DDL events are raised and if you have triggers associated with those events, they are fired automatically
Not only the DML statements are going to fire, DDL triggers. We also have system stored procedures that perform DDL like operations and these systems stored procedure can also fire DDL triggers.
One Such system stored procedure is sp_rename. We use sp_rename system stored procedure to rename a database object.
For example, we can use it to rename a table or a column in a table. So whenever we do that using the system procedure, it’s going to raise the rename event and if you have a trigger associated with that event, it will be fired automatically when you rename an object.
What are the uses of DDL triggers?
There are several uses of DDL triggers .
- For example if you want to execute some code in response to a specific DDL event, you can do that because the triggers are fired in response to DDL events. So you can put whatever you want to execute within the body of that trigger. And whenever you create a table, create_table event is raised and the associated trigger is going to execute that code.
- Similarly, if you want to prevent changes to your database schema. You can use a DDL trigger. For example, let’s say you want to prevent users from creating, altering, or dropping tables. You can do that using a DDL trigger.
- you can also use a trigger to audit the changes that the users are making to the database structure. Now, let’s say whatever changes people are making to the database schema, I want to capture all those changes and I want to audit those changes and maybe store them in a table.We can very easily achieve that using a DDL trigger.
- For example, if somebody modifies a table, I want to capture information like what is the login name of the user who modified that?What is the name of the database in which that table is present? What is the DateTime? who did that modification and What is the name of the table? . What is the exact statement that they have executed to do that modification? All that information can be captured using a trigger.
Syntax for creating a DDL trigger
here we have the syntax for creating a DDL trigger. So here, we start with create trigger and then we have the trigger name, and then we use the ‘on‘ keyword and then we specify the scope of the trigger.
We can create DDL triggers in a specific database or a server-wide trigger. If you want to create a trigger whose scope is the database, then you use the database keyword otherwise, server.
Database scoped DDL triggers
CREATE TRIGGER [Your_Trigger_Name] ON [Trigger Scope (Server OR Database)] FOR [Event1, Event2, Event3, ...Eventn], AS BEGIN -- Your Trigger Body END
So if you want this triggers to be fired for three events, Create_table, drop_table, and alter_table, You simply separate those events using a comma and then you use the “AS” would begin , END within begin and END you’ll have your trigger body.
Let’s look at an example now. So here we have a very simple example. Create trigger the name of the trigger and then we use the ON Keywood and database SCOPE.
CREATE TRIGGER trOnTableCreateTrigger ON Database FOR CREATE_TABLE AS BEGIN Print 'You have created a table in database' END
we are creating a trigger whose SCOPE is database and then for CREATE_TABLE. So the name of the event is CREATE_TABLE.
whenever you execute the create table DML statement, CREATE_TABLE event is raised and we have a trigger associated with that event. So whenever you create a table, this trigger will be automatically be fired. That’s going to print this message. You have created a table in database
How to find database scoped ddl triggers in sql server
Go to the programmability folder and within that expand database triggers. If you can’t find the trigger that you have just created.
Right-Click on that and select refresh from the context menu and you should find the trigger.
So that’s our trigger.
Now here we have created a table statement, which is going to create a table with one column. So when we execute this, it should automatically print a message because this trOnTableCreateTrigger trigger will be fired.
So trigger is fired in response to a single event. Now, let’s say I want this trigger to be fired for alter and drop table events as well. If that’s the case, you simply need to separate the event names using a comma.
ALTER TRIGGER trOnTableCreateTrigger ON Database FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE AS BEGIN Print 'New table created or modified' END
Now if you try to create, alter or drop a table, the DDL trigger will fire and you will see the text.
Another use of triggers is that you can prevent certain changes to your database schema. let’s say whenever somebody tries to create or alter or drop a table, I want I don’t want that to happen
I want to prevent those changes. I can do that using a DDL trigger. So within the trigger, I am simply going to say rollback.
CREATE TRIGGER [trOnTableCreateTrigger] ON Database FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE AS BEGIN Rollback Print 'You do not have permission to change the database' END GO
Now if you try to create, alter or drop a table, the DDL trigger will fire and you will see the below message.
Now the only way to create, alter or drop a table is by either disabling the trigger or deleting that trigger. And to disable the trigger, you can use the simple command
- DISABLE TRIGGER trOnTableCreateTrigger ON DATABASE
- ENABLE TRIGGER trOnTableCreateTrigger ON DATABASE
- DROP TRIGGER trOnTableCreateTrigger ON DATABASE
Server scoped DDL triggers
Here we have a database scoped Trigger . We have specified the scope as a database. and look at what the trigger is doing. It’s preventing users from creating, altering, or dropping a table.
ALTER TRIGGER [trOnTableCreateTrigger] ON Database FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE AS BEGIN Rollback Print 'You do not have permission to change the database' END GO
Let’s we have two databaste in our server DemoDB and Demo Database.
We created this trigger statement within the context of the DemoDB database. So this trigger will be now created in that database. And if we try to create a table within the DemoDB database, that trigger should prevent us from doing that. we get the error message.
Now, this trigger is present only within that DemoDB database because this is a trigger that is scoped to that database.
I have another database in our server. Now, if I try to create a table within that database, will I be allowed to do that? Yes, When I execute create table statement in the context of Demo, notice that we can create the table without any problem.
Now let’s say for some reason, even in the demo database or in all database in our server, we want to prevent users from creating, altering, or dropping tables.
Now, one way to achieve this is by creating same trigger in all database. This approach is OK if we have just one or two databases.
Now, imagine if we have 100 databases on instance of SQL Server. And in all those 100 different databases, we want to prevent users from creating, altering, or dropping tables.
Now, in this case, definitely creating, the same trigger in all those 100 different databases is not the right approach. And it’s not right for two reasons.
One, it is tedious and error-prone to maintainability is going to be a nightmare, because if we have to change the logic and the trigger, then we will have to do the change in all the 100 different databases, which again, is going to be tedious and error-prone.
So this is the case when server code triggers are going to come in handy.Creating server code triggers is very similar to creating database scoped triggers. All you have to do is change the scope from the database to all servers. So let’s create a server scoped to trigger first.
Server Scoped DDL Trigger
CREATE TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS BEGIN ROLLBACK Print 'You do not have permission to change the on the server' END
Now let’s go ahead and execute this create trigger statement. where does this trigger create?
This is actually created on the server level. So we have the server objects folder. If we expand that, we have got triggers folder and when we expand that, we can find our server scoped trigger there.
Now let’s try to create a table within DemoDB database, but we should still be prevented from doing that.
Let’s try to create a table with a Demo database, but we should still be prevented from doing that.
Now here we are using a SQL command to do that. Now, if I try to delete the table using the graphical user interface of SQL Server Management Studio, will I be allowed to do that?No.
When I try to delete it by clicking on that and selecting delete and when I click, OK, I notice that we still get an error message.
And if you look at what the error message is, it says drop fail for table test.
And here’s the message. The transaction ended in the trigger. We get the same error message. Irrespective of whether you use a graphical user interface or a SQL command, you will still not be able to do that because the trigger is preventing it right now to create a trigger.
How disable Server-scoped DDL trigger?
We can easily disable the trigger using the below SQL command
DISABLE TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER
How to enable the Server-scoped DDL trigger?
We can easily disable the trigger using the below SQL command
ENABLE TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER
How to drop the Server-scoped DDL trigger?
We can easily disable the trigger using the below SQL command
DROP TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER
The post DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?