Triggers : quick recap SQL

Read Time:2 Minute, 27 Second

Trigger: A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]

create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
[before | after]: This specifies when the trigger will be executed.
{insert | update | delete}: This specifies the DML operation.
on [table_name]: This specifies the name of the table associated with the trigger.
[for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
[trigger_body]: This provides the operation to be performed as trigger is fired

Example:
Given Student Report Database, in which student marks assessment is recorded. In such schema, create a trigger so that the total and percentage of specified marks is automatically inserted whenever a record is insert.
Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.
SQL Trigger to problem statement.
create trigger stud_marks
before INSERT
on
Student
for each row
set Student.total = Student.subj1 + Student.subj2 + Student.subj3, Student.per = Student.total * 60 / 100;

mysql> select * from Student;
+—–+——-+——-+——-+——-+——-+——+
| tid | name | subj1 | subj2 | subj3 | total | per |
+—–+——-+——-+——-+——-+——-+——+
| 100 | ABCDE | 20 | 20 | 20 | 60 | 36 |
+—–+——-+——-+——-+——-+——-+——+
1 row in set (0.00 sec)

In this way trigger can be creates and executed in the databases.

Code of a DDL Trigger
The DDL triggers are fired in response to DDL (Data Definition Language) command events that start with Create, Alter and Drop, such as Create_table, Create_view, drop_table, Drop_view and Alter_table.
create trigger saftey
on database
for
create_table,alter_table,drop_table
as
print'you can not create ,drop and alter table in this database'
rollback;

DML Triggers
The DML triggers are fired in response to DML (Data Manipulation Language) command events that start with Insert, Update, and Delete. Like insert_table, Update_view and Delete_table.
create trigger deep
on emp
for
insert,update,delete
as
print'you can not insert,update and delete this table i'
rollback;

There are two types of DML triggers
AFTER Triggers
AFTER triggers are executed after the action of an INSERT, UPDATE, or DELETE statement.
create trigger insertt
on emp
after insert
as
begin
insert into empstatus values('active')
end

INSTEAD Of Triggers
It will tell the database engine to execute the trigger instead of executing the statement. For example an insert trigger executes when an event occurs instead of the statement that would insert the values in the table .
CREATE TRIGGER instoftr
ON v11
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO emp
SELECT I.id, I.names
FROM INSERTED I

INSERT INTO emp1values
SELECT I.id1, I.name1
FROM INSERTED I
END

An INSTEAD OF trigger is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.