TRIGGERS

A trigger is a special type of procedure that will used to provide restrict on the tables and database when a language commands executed. Triggers are four types DML, DDL CLR and Login triggers, but today, we will discuss only the following topics:

  • DML Triggers
  • DDL Triggers

DML Trigger:

DML triggers executes when the user tries modifying or change data through manipulation language commands. Those are insert, update and delete statements on the table.

DML triggers can be used to enforce business rules and data integrity with the help of a DML trigger we can enforce integrity, which cannot be done with constraints.

Syntax:

CREATE [OR ALTER] TRIGGER <TNAME> ON <TN>
FOR|AFTER <INSERT/UPDATE/DELETE>

AS
BEGIN
<TRIGGER BODY /STATEMENT>
END

Note:

Trigger are invoking or triggering on a table automatically when we perform the DML operations on the table. When cannot pass parameter less objects. We prepare a sample database with table called TRCOURSE by using following T-SQL statements.

Creating table TRCOURSE as follows:

To verify that everything is working properly we will do some operations on the table as follows:

Now we can take an example to make clear, for restricting operation on a table by using Trigger object as shown below:

Example on Insert Operation:

Checking:

Here UPDATING, DELETING operations can be done because these two operations are not restricted. So we can do it as shown below:

As we did before, we can create individuals restriction operations or all at the time. Now let us create update and delete operations at same time as following:

Checking:

As we mention before, triggers are special database object, which are used to control operations on database on specific command or time. Until now, we were using command restriction triggers so here we will see a restriction based on a specific date or time.

For example, we will restrict Wednesday operation only, whereas other days operations are ok as follows:

In order to check this trigger is working or not first, we have to drop one of the restriction , which we did before as follows:

As we know Operating System has its own specification for date and time format, for me today is Wednesday which corresponding to 4 day for OS as shown figure below:

Because of this, we write a procedure like this:

Now if we checking trigger restriction it will be as below:

Also let us see trigger to restrict on specific time. For instant, DML Trigger to restrict before 3 PM.

Checking:

Alternatively, DML triggers can be created between two interval times. For example, we will create a DML restriction operation on a table in between 9 AM to 5 PM as shown below:

In order to check first we must drop a trigger, which effect this operation.

Checking:

Syntax to Drop DML Triggers:

DROP TRIGGER <TRNAME>

Example:

DDL TRIGGERS:

These trigger are used to restrict CREATE, ALTER, and DROP commands on a particular DB in SQL Server.

Syntax:

CREATE [OR ALTER] TRIGGER <TNAME> ON DATABASE
FOR / AFTER <CREATE_TABLE / ALTER_TABLE /DROP_TABLE>
AS
BEGIN
<TRIGGER BODY / STATEMENTS>
END

Example:

We will create a trigger to restrict the DDL operations [create, alter, drop] on TRSAMPLE database.

Checking:

Syntax to Drop DDL Triggers:

DROP TRIGGER <TRNAME> ON DATABASE

Example:

Note:

Triggers are very tricky to understand because of sometimes you people may need to DELETE previous trigger in order to get proper work of new trigger restriction so be careful what you are doing during steps.

Next week we will see what is Magic Table and how it works?

Finally,thank you guys for your time and make sure you give like and share your friends to get next upcoming articles. Be in touch and see you soon.

Latest Articles:

Follow us:

Leave a Reply

Your email address will not be published. Required fields are marked *