DDL Triggers In Sql Server with Example

There are four types of triggers in the SQL server. First of all, we’ve got DML triggers. DML stands for Data Manipulation Language. We’ll talk about these triggers in the next article.

And then there are the DDL triggers, DDL stands for Data Definition Language. In this post we will talk about DDL-triggers and then we have CLR-triggers, CLR stands for Common Language Runtime. And finally, the connection is activated.

1. DML trigger
2. CLR triggers
3. Activating the connection

What is an LDD tractor?

DDL triggers are executed in response to DDL events. So the next obvious question that comes to mind is what FDL events are and when they occur.

Each time you create, modify or delete an object from the database, the corresponding DDL event occurs. For example, if you create a table using the DDL create table operator, the corresponding event is create_table. So the event is over.

And if you have a trigger related to this event, this trigger will be activated automatically when you make a painting.

In the same way, the event drop_procedure is triggered when you start a saved procedure. When a function is created, the DDL event create_function is increased.

For a complete list of DDL events, see the link MSDN – MSDN ddl events.

This means that when you run DML returns that refer to DDL events, they are triggered, and if you have triggers that refer to these events, they are triggered automatically.

Not only the operators of the HML are shot, but also the triggers of the DDL. We also have procedures stored in the system that perform such LDD operations, and these procedures can also trigger LDD triggers.

One of these procedures stored in the system is sp_rename. We use the saved sp_rename procedure to rename the database object.

For example, we can use it to rename a table or a column in a table. Therefore, if you use the system procedure, the renaming event is triggered, and if you linked a trigger to this event, it is automatically triggered when the object gets a new name.

What are the applications of DDL triggers?

There are several applications for DDL triggers.

• For example, if you want to run a certain piece of code in response to a specific DDL event, you can do this because the triggers are triggered in response to a DDL event. So you can put anything you want in the trigger body. And each time you create a table, the create_table event is incremented and the corresponding trigger executes this code.
• Also if you want to avoid changes in your database schedule. You can use a DDL tractor. Suppose you want to prohibit users from creating, modifying or deleting tables. You can do this using a DDL tractor.
• You can also use the trigger to check the changes that users have made to the database structure. Suppose I want to record all the changes people make to the database scheme, and I want to look at those changes and maybe record them in a table. We can do this very easily using a DDL tractor.
• For example, when someone changes a table, I want to get information such as the name of the user who changed the table and the name of the database where the table is located. What is the date and time? Who made this change and what is the name of the table? . What’s the exact explanation they gave for this change? All this information can be intercepted using a trigger.

Syntax for making DDL triggers

Here we have the syntax to make a DDL-tractor. So here we start by creating the trigger, then we have the name of the trigger, then we use the keyword on it, and then we specify the scope of the trigger.

We can create DDL triggers in a specific database or server-wide triggers. If you want to create a trigger with a database as scope, use the database keyword differently, namely Server.

DDL tractor for database

CREATE TRIGGER [your_trigger_name] ON [trigger area (server or database)] FOR [Event1, Event2, Event3, …Ereignistn],
AS
BEGIN
— Your main part of trigger
END

So if you want three events, create_table, drop_table and change_table, to trigger this trigger, separate these events with a comma, use AS, then start, END is in the beginning and END has your trigger body.

Now let’s look at an example. So we have a very simple example. Create a trigger name and we will use ON Keywood and the SCOPE database.

CREATE TRIGGER trOnTableCreateTrigger
ON Database
FOR CREATE_TABLE
AS
BEGIN
Print You have created a table in database
END.

we create a trigger of which the SCOPE is the database, then for CREATE_TABLE the name of the event is CREATE_TABLE.

Each time you run the DML table creation operator, the CREATE_TABLE event is triggered and we have a trigger linked to this event. So, every time you make a painting, this trigger is activated automatically. This message will be printed. You have created a table in a database

How to find ddl triggers in the sql server

Scroll down to the Programmability folder and inside to the Database Extension triggers. If you can’t find the trigger you just created.

Right click on it and select Update from the context menu, after which you should find the trigger.

So that’s our trigger.

Here we have now created a table operator who creates a table with a column. If we do this, the message should be printed automatically because this trOnTableCreateTrigger is running.

Therefore, the trigger is activated in response to a single event. Let’s say I want this trigger to be activated when the table changes and events are reset. If this is the case, separate the names of the events with a comma.

ALTER TRIGGER trOnTableCreateTrigger
in database
for CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
Start
Print ‘Newly created or modified table’
END

If you now try to create, edit or delete a table, the DDL trigger is activated and you will see the text.

Another use of triggers is to enable you to prevent certain changes in your database schedule. For example, if someone tries to create or modify or delete an array, I don’t want that to happen.

I want to avoid these changes. I can do it with a DDL tractor. Now, inside the trigger, I’m just telling you to go back.

TREATE TRIGGER
In
DB for CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
Start
Rollback
Print ‘You may not modify the database’
END
GO

If you now try to create, modify or delete a table, the DDL trigger will be activated and you will see the message below.

From now on, the only way to create, modify or delete an array is to disable or remove the trigger. And to turn off the trigger, you can use a simple command.

• RUNNING PIPE
• ENAMELLED TUBE TUBE TUBE TUBE TUBE TUBE TUBE ON DATA
• DROP TRIGGER trOnTrigger on DATABASE

DDLtriggers recognized by server

We have here a database intercepted by a trigger. We specified the field as a database. Let’s see what the trigger does. This prevents users from creating, modifying or deleting the table.

ALTER TRIGGER [trOnTableCreateTrigger] In database
for CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
Start
Rollback
Print ‘You cannot change the database’
END
GO

We have two databases on our DemoDB server and the demo database.

We made this trigger statement in combination with the DemoDB database. This trigger is now created in this database. And if we try to create a table in the DemoDB database, this trigger won’t let us. We’re getting an error message.

Now, this trigger is only present in the DemoDB database, because it is a trigger that is located in this database.

I have another database on our server. Now, if I try to create a table in this database, can I do that? Yes, if I output the table creation operator as part of the demo, please note that we can create a table without any problems.

Suppose for some reason we want to prevent users from creating, changing or deleting tables, even in the demo database or in the entire database on our server.

One way to do this is to create the same trigger throughout the database. This approach is normal if we only have one or two databases.

Now imagine we have 100 databases on an SQL server. And in all those 100 different databases, we want to prohibit users from creating, modifying or deleting tables.

Well, in this case, it’s not the right approach to definitively create the same trigger in all those 100 different databases. And that’s wrong for two reasons.

First, it is annoying and error-prone, because if we have to change the logic and the trigger, we have to make changes in the 100 different databases, which is annoying and error-prone.

Creating server code triggers is very similar to creating triggers that cover the database. Simply change the size of the database to all servers. So let’s create a server that is scanned first to trigger.

Server range Triggering the DDL

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 on the server’
END.

Now let’s perform the operation to create a trigger. Where is this trigger created?

In fact, it is created at the server level. Okay, we’ve got the Objects folder from the server. If we implement it, we have a trigger map, and if we implement it, we can find the trigger in the area of our server.

Now we want to try to create a table in the DemoDB database, but we shouldn’t do that.

Let’s try to create a table with the demo database, but we shouldn’t do that.

We now use the SQL command for this. Now, if I try to delete a table with the SQL Server Management Studio GUI, can I do that?

When I try to delete them by clicking on them and choosing to delete them, and when I click OK, I see that we always get an error message.

And if you look at the error message, you’ll see that the drop error was written for the table test…

And here’s the message. The deal ended with a trigger. We’re getting the same error message. Whether you use the GUI or the SQL command, you still can’t do it because you can’t create a trigger now.

How do I disable a server-controlled DDL tractor?

We can easily disable the trigger with the following SQL command

STATEMENT TRIIGGER tr_ServerScopeTblTrigger on all servers

How do I activate a server-controlled ADSL trigger?

We can easily disable the trigger with the following SQL command

TRIGGER tr_ServerScopeTblTrigger ACTIVE ON ALL SERVERS

How do I reset a server-managed DDL tractor?

We can easily disable the trigger with the following SQL command

DROP TRIGGER tr_ServerScopeTblTrigger on all servers

Related Tags: