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
- An expression of non-boolean type specified in a context where a condition is expected
- Using custom codification scheme instead of GUID as Primary Key
- empty field should not be displayed in a separate column
- Cannot connect to local instance of SQL Server 2008
- Printing variables and messages in T-Sql in SQL Server
- What is the format of TSQL STATS_STREAM (undocumented feature)? Does it contain all the Statistics Data?
- sql server: Estimated number of rows is way off
- SQL Query to get largest datatype in schema
- Hierarchical SQL query not returning level
- Is `Delete From Join` Standard SQL?
- SQL Query - Delete duplicates if more than 3 dups?
- Extracting data based on data in multiple columns and rows
- How to make awkward pivot of sql table in SQL Server 2005?
- Convert Single row to Multiple rows in SQL Server
- Query to find the maximum relation between two fields
- Insert into table only distinct rows?
- Like Operator for checking multiple words
- How to link Report Server Subscription SQL Agent Job name to the name of the report it is running
- Entity framework (core), save incomplete model containing required fields
- Why Backup database is terminating abnormally in WPF with c#?
- Concatenation of integers with commas in SQL Server?
- Include subselect only if there is one result using tsql
- decimal value from db displayed in textbox
- Linking SQL Azure and SQL Server
- Creating a column that'll show previous rates
- Hibernate wont write correctly floating point numbers to a MS SQL 2005 database
- Why do we always prefer using parameters in SQL statements?
- How to select a primary key which has exact foreign keys matches a given list of values?
- Sql Query and Double Var Values
- SQL Server Outlier Filter
- Logic to give me first row if a certain condition is met in Microsoft SQL Server Management Studio 2012
- How to Add a default value in SQL server compact?
- SQL geography::EnvelopeAggregate output not accurate
- SQL Merge with inserting into the third table
- Tracking data changes per table column in T-SQL
- How do I escape a single quote in SQL Server?
- adding a constraint to a column referenced to another table's column
- Need help to optimize potentially erroneous spatial SQL query
- Give System Managed Identity access to classic SQL Server
- Sql Query to Compare Same Field having differnet values because of Group Clause
- Saving files to varbinary(max) field?
- SQL Server shows "Invalid object name '#temp'" when working with a temporary table
- Difference between a linked server and a synonym?
- Access 2007 SQL query to ignore 0 value
- What kind of datatype should one use to store hashes?
- About subquery in T-SQL
- Importing data from Excel through stored procedure
- What is the SQL Server equivalent of the following Access statement
- NHibernate Bag Mapping
- stored procedures and testing -- still a problem even today. Why?
- Select from table with object_id
- SQL Server vs. Access SQL User Input
- NPOCO. Is it possible to insert nested objects?
- How to implement multi relationship in SQL Server?
- sql server log messages abouts logins
- datename conversion fails in SQL agent only
- tiny_tds failed at the second execute
- SQL - Return first non-empty value for previous days
- Intersection and consolidation of time periods in SQL
- How to force SQL Server to return empty JSON array
- How can I backup connections in SQL Server Management Studio
- How do databases physically store data on a filesystem?
- SQL using REPLACE on one of many columns
- How to get skip level manager details in sql?
- Recover database between unit tests: Database is still in use
- How can I split a year range into year rows in SQL?
- SQL Server : data between specific range
- When will a FAST_FORWARD cursor have a work table (and is this something to avoid)?
- Problem with distributed Computing with mdf files
- restoring original MDF file from bak file
- Run SQL script inside Visual Studio
- SQL Server : splitting the results of GROUP BY into a separate columns
- Issue with `DBI::dbGetQuery` run from a Shiny app
- How to reset a variable/sql record once a month automatically with asp.net/sql ?
- Do a select depending of result of a another select
- Generate database using another database shema information using c#
- Get Dates Every Year Between 2 Dates SQL
- TSQL merge 2 dataset with even number of rows next to eachother
- How big teams work with database
- SQL query to return most recent data per group, then active/inactive per different group
- Create database with name and path from variable
- SQL Performance, Using OPTION (FAST n)
- Find the percentage of Non-Null rows in SQL
- Best way to select nonclustered index on SQL Server 2014
- MS SQL Server bit column exported as boolean
- Full Text search on varbinary(max) column is not working
- Stored procedure not executing correctly through entity framework
- Convert an integer to time, then query if current time is past that integer
- SCHEMA_NAME() returns NULL
- It it possible to Trace who deleted records in SQL Table?
- Error when uploading my database from SQL server 2017 to Azure SQL server
- SQL to find maximum value and then make a decision, which name has the greatest value
- SQL / Excel: Make Excel table of SQL entries per hour
- MS-SQL - Extracting numerical portion of a string
- how to load both csv and excel file in sql server using single connection manager in SSIS?
- Reading JSON in SQL SERVER 2012
- SQL interpolate missing dates
- Speed File System vs. Database for Frequent Data Processing
- SQL Server Spatial Issue
- Tweaking the Fill Factor to reduce fragmentation
- SQL conditional grouping without using cursors
- How can I return the second column/property of an object in PowerShell?
- Package Execution via POWERSHELL
- How to create/add columns using a variable in a loop
- Create a view using SQL Server with repeating rows and new column
- Stored Procedure for saving into two table
- Serverside Technologies and Flow
- How to find the days compare with date?
- Removing Leading and Trailing Zeros along with a Case
- New field with the date of the subsequent row
- How to insert multiple rows - a loop needed?
- Return condition (in operator) in case statement in SQL
- Extract a value from SQL XML field
- Taking median of calculation in SQL Server
- Rollback changes made to Database in Entity Framework .NET
- SQL Current month/ year question
- How can i use math operations in Aggregate Functions?
- How do we use a LINQ query on a Dictionary with a nested object?
- Using column value as column name in subquery
- Can't gain access to local SQL for modification
- Join with comma separated values in SQL Server Compact not working
- SQL Server Convert Table Contents into Merge Statement?
- Retrieve latest rows based on the condition and its previous rows action column on a given date
- how to use datepart with date in sql server
- Connecting to Azure SQL Server with Qt on Linux
- Pandas apply speed on large datasets.
- WHERE clause on VARCHAR column seems to operate as a LIKE
- SQL query, what Items are in a bucket?
- Is Microsoft SQL Server Express available for production in Linux?
- Embedding dates in SQL queries in R using sprintf
- SQL Server Insert Without INTO
- How to use Table Variable in Dynamic Query
- Selecting Oracle Stored Procedure in SSRS Crashes Visual Studio 2005 IDE
- Slow Query with Dynamic WHERE Clause
- How to schedule data insertion from dbf to SQL Server on 64-bit Windows Server 2012
- How to copy a column value to other column of same database table whenever a new record is inserted?
- Not allowing deletion of records by any user except through stored procedure
- Using Query on SQL Server database to count records of some type and grouped by some foreign key
- Spark - jdbc write fails in Yarn cluster mode but works in spark-shell
- Can I script Data Classifications in SQL Azure
- combine rows into one row in tsql
- SQL Update row from select Fails
- SQL Server procedure with 2 arguments - why doesn't work?
- C# Form not inserting values into SQL Server database
- how to get a comma delimited list from 2 columns in a table
- How to get data based on two columns from same table in SQL
- "Table does not contain primary key"
- Howto build a SQL statement with using IDs that might not be available in the table?
- alter mssql column to add auto increment without t-sql
- How does one retrieve records with all days in date range covered?