What is Trigger in SQL | Trigger in PLSQL | Types of Triggers

We all are familiar with the programming languages like C, JAVA, C# and so more available. One of them is PLSQL Language. If you have learned this language then it’s better for you. But I know you are here to find What is Trigger in SQL.

You can find various definition’s on google defining about Triggers in PLSQL or Triggers in SQL. I have also write something on this with my opinion.  I have explained it in very easy words so that you can easily understand it.

Why I am writing on this? Well, I have this language in my academic year, so I am familiar with it. Before turning towards What is Trigger in SQL. I will give you little information about what actually trigger is? for what the trigger word stand for?

Let’s understand what actually Trigger is? You might have use this word in your day to day life. Wikipedia says that Trigger is “a small device that releases a spring or catch and so sets off a mechanism“.

Have you ever heard about “Gun Trigger”? Yes, you have right? This Trigger in SQL also works same as that like a simple mechanism.

What is Trigger in SQL?

When I was new in learning PLSQL Programming that time I have search lot about Triggers like :

what is trigger in sql

what is trigger in mysql

what is trigger in oracle

what is trigger in plsql

After searching all these different queries I always get the same answers. You might me thinking how fool I am 😀 . Well It was my first time so that I did not get PLSQL Triggers easily.

So what actually trigger is? and in which field it actually occurs? After lot of searching I have find that Triggers are the part of PLSQL Language. Here’s the actual definition:

What is Triggers in PLSQL :

Triggers is a special mechanism that is invoked by oracle engine automatically when an specific event occurs. These Triggers are the PLSQL blocks and store in database.
It is a specialised program get executed when triggering event occurs.

Here the triggering event can be :

  • DML Statement
  • DDL Statement
  • System Event
  • User Event

This is the short and easy definition of the Triggers in PLSQL.

What are Advantages of Triggers in SQL:

  1. Preventing Invalid Transactions.
  2. Synchronous replication of tables.
  3. Storing Information of Table Access.
  4. Generates derived column value automatically.

These are some Advantages of Triggers in SQL.

I hope that you have understand the exact definition of What is Trigger in SQL.

Now lets turn towards Types of Triggers in SQL.

Types of Triggers:

Types of Triggers

DML Triggers : These are the PLSQL Triggers which are depends on the DML Statements such as Update, Insert or Delete. These Triggers can be get fired before or after the statements.

DDL Triggers : These are the PLSQL Triggers which are created over DDL Statements such as Create or Alter. Such type of Triggers you can monitor the behaviours on your DDL Statements.

System Events Triggers : These are the Types of Triggers which come into action when system event occurs such as database log-on or log-off.

Compound Triggers : These triggers are the multi tasking triggers that act as both statements as well as row level triggers when the data in Inserted, Updated or Deleted from a table. Compound Triggers can be use to Audit and Check the values.

These are the main Types of Trigger that are mostly in use.

Syntax of Triggers:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

BEGIN

— sql statements

END;

So these are all about What is Trigger in SQL or Trigger in PLSQL and the Types of Triggers.
If you have any difficulties then you can comment below I will try to help you out.

Below are some examples of triggers:

PL/SQL Trigger to Allow User to Make Changes Between 8am to 5pm

PL/SQL Trigger to Restrict User to Make Changes After 5pm

PL/SQL Trigger to Restrict User to Make Changes on Sunday

PL/SQL Trigger to Insert Salary more than 10000

PL/SQL Trigger to Insert Records in Lowercase

PL/SQL Trigger to Insert Records in Uppercase

PL/SQL Trigger to Insert Deleted Record in New Table

 

 

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.