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
- SQL Server MAXDOP tuning in Virtual Environment
- Must declare the scalar variable "@ID" for insert parameter
- Grouping rows with each different row has header row SQL Server
- Stored Procedure Query Optimization
- sql server query partitioned table
- How to Query A DataGridView Using Linq
- How to Loop Through Specific rows in T-SQL
- SQL Server email trigger upon data insertion into a table not working
- Activity Monitor query
- How to Bulk insert Hindi Text in SQL Server
- Add column to table and then update it inside transaction
- get full hits information of site with asp.net
- Avoiding name collisions when creating external tables on SQL Server datawarehouse
- Linq joined entity is null
- Merge Replication - Invalid Column Name
- Create "Many to Many" relationship between multiple tables with Sequelize
- sqlsrv_connect() not found
- SQL Server : select from xml with namespace
- De-normalize table or use Joins in Entity Framework
- Create a new column in my SQL View - Column must be two other columns combined
- Parse Json in SQL Server
- Is use of while loop in stored procedure causing System.OutOfMemoryException?
- cursor over multivalue reporting services parameter
- Error converting data type nvarchar to numeric. Tried convert and replace
- Why can't I connect/register SQL Server 2005 on port 1433 TCP?
- aspnet_Users table with huge indexsize
- Exporting table in a specific SQL database to a new database
- How can I convert a string to a json string with SQL Server
- Why is ASP.NET ignoring my Membership connection string?
- Reducing impact on server load caused by long but non-priority adhoc queries
- SQL how to deal with no values
- In SQL Server, how can I insert data into a table that has just one column which is of identity type?
- Reduce Azure SQL Database Size
- Cannot connect remotely to a SQL Server named instance
- Using Coalesce in Where Statement
- Split text into 4 characters with no delimiter
- T-SQL Stored Procedure passing input, using output
- Maximum number of nvarchar(max) columns in sql server tables
- Converting DateTime to AM/PM Time
- Fast interaction with database
- Re-writing this sql query
- how to split Json data present in one column of table in mssql for generating reports using ssrs
- Removing Space from Stored Table row with Procedure in SQL
- SQL exists returns all rows
- How to efficiently loop through using Sql query
- sqlcmd does not create parameters on a backup file name when executed through Powershell or Command line
- How to loop through SQL table and find rows that match your IF conditions?
- Combining data from one table into a 2nd table
- Migrations for PostgreSQL
- How to use table variable in dynamic sql? OR create temporary table from user defined table type?
- Joining sys.columns and sys.tables on database name
- Query is taking different time in every execution in SQL Server
- EF Code First Migrations and Foreign Key Constraints
- Powershell -contains/-notcontains - having trouble with an array result from SQLServer
- Calculating time between entries in sql
- create_date / modify_date not accurate when export to EXCEL
- Migrate SQL Server database to multiple files
- TSQL Insert the column default value from Case Statement
- How can I use multiple parameters using pandas pd.read_sql_query?
- Adding a numeric value to all rows of a column SQL Server
- Pivot a table with data in a one-to-many relationship without aggregating?
- Is Data Reader better or Data Set for application where we may have Concurrency issue
- SQL Server : calculate column value based on value in previous row/id
- SQL Case Sensitive Group By
- Pulling combined results from 'hierarchical' data in a table
- T-SQL Substring Replace (Translate in 2017)
- How to find a substring in given string?
- Help to the way to write a query for the requirement
- How to retrieve only integer values from SQL Server
- Is there a setting in SQL Server to have null = null evaluate to true?
- What performs better: multiple single-result sql server stored procedures or single multple-result stored procedure
- database design pattern: many to many relationship across tables?
- order by slows query down massively
- How we get local SQL Server instance through c#
- Maximum of the count of the grouped elements
- How to get last 7 days data from current datetime to last 7 days in sql server
- Enable an identity column that already has data in it?
- How does SQL server compress NULL bit datatypes?
- How can I restore a postgresql dump to a SQL Server instance?
- Azure SQL: Keep alive option
- List all Dates between two Date columns of a table
- Split Name into Last name, First Name, Middle Name in SQL
- Can you scale SQL Server to handle 100's of Terabytes?
- How to change DT_STR to DT_WSTR by default in SSIS for Oracle source
- SQL Server Stored Procedure with varchar parameter returns wrong results
- How to select specific records of groups based on criteria
- Insert a row for every row in a UDT
- How to sum dynamic columns in SQL Server?
- assigning a count of 0 to a row with no data
- Verify data integrity for varbinary column
- Why the output parameter of my ADODB.Command does not retrieve a value when executing?
- SQL Regex get phone number
- Using MSSQL 2016 "AT TIMEZONE" feature from Entity Framework?
- DATEFORMAT FUNCTION IN SQL
- Get datetime on MSSQL
- Where Clause Rejecting Rows if NULL occurred
- SSIS ForEach - Get current value of collection
- Can I pass Cypress.env inside of plugin/index.js file?
- SQL Server - Batch Processing and 1 second waits
- Connection string between IIS and SQL server
- how do i get the rowcount of table variables?
- VARCHAR' is not a recognized built-in function name
- How to bind query parameters in hibernate if query is dynamic statement?
- Meaning of words colored with Blue and Pink in MSQLSMS
- Change run action in Visual Studio database project
- Finding max value for a column containing hierarchical decimals
- How to save a string in SQL Server as SqlDateType by using Visual Studio 2013?
- SQL Server DateDiff between two dates returns inaccurate values
- Last time value changed from negative to positive
- ZIP files not working in SSIS (server level issue?)
- Is there a tool to convert T-SQL Stored Procedures to C#?
- Group By varbinary (MAX) or varchar (MAX) in SQL Server
- Query for two most recent order dates in separate columns
- Create a global variable list/set in sql server
- VBA in Access 2010 - Run-time Error 430
- How can I update the end date to the value of the following records start date?
- How should I query key-value pairs in this scenario?
- How Can I Easily Document a Database Environment?
- How do I remove the first characters of a specific column in a table?
- lu.[TableName] instead of dbo.[TableName]?
- Find max value from coalesce function
- How to use DATEPART of the GETDATE as a variable for a query
- How to set datetime field value to now
- Which query is used to reseed identity primary key in sql server 2012 since DBCC CHECKIDENT (mytable, RESEED, 1) not working in 2012?
- Cannot use Stored Procedure in SQL Server 2012 within VS2013 Report Designer, always returns blank?
- How to generate sql scripts using SMO scripter
- error converting varchar to float
- Using table auditing in order to have "snapshots" of table
- ASP.NET Core: how to hide database ids?
- How many criteria matched?
- SQL recursively creating matching groups based on reference table
- How can I update a table with data retrieved by a join on itself?
- How to prevent SQL71609 with SSDT on a new temporal table (system-versioned)
- Populating rows based on the value in that row
- Insert two values to two column in SQL Server from one Excel row
- Stored Queries in access vs. Stored Procedure in MS SQL Server
- How can we query data from two different databases and compare the results using SQL Server?
- How do I compare 2 rows from the same table (SQL Server)?
- SQL derived column call number
- Select statement for getting the later date
- Permission Denied Accessing SQL Server Master Database from Windows Service
- Adding a nullable rowversion column to a table
- Getting the Sum of the Maximums instead of Maximum of Sums
- is there a way to monitor transactional replication latency without using tokens?
- Do Math on Two Scalar Queries in SQL
- Using parameterized function calls in SELECT statements. SQL Server
- Grouping other fields by common email
- Returning a certain number of results from the top
- Update SQL Server Database Schema with software update
- Accessing Azure SQL database from anywhere