Skip to content
Related Articles

Related Articles

Improve Article

Difference between Assertions and Triggers in DBMS

  • Difficulty Level : Medium
  • Last Updated : 14 Jul, 2020

1. What are Assertions ?
When a constraint involves 2 (or) more tables, the table constraint mechanism is sometimes hard and results may not come as expected. To cover such situation SQL supports the creation of assertions which are constraints not associated with only one table. And an assertion statement should ensure a certain condition will always exist in the database. DBMS always checks the assertion whenever modifications are done in the corresponding table.

Syntax –

CREATE ASSERTION  [ assertion_name ]
CHECK ( [ condition ] );

Example –

CREATE TABLE sailors (sid int,sname varchar(20), rating int,primary key(sid),
CHECK(rating >= 1 AND rating <=10)
CHECK((select count(s.sid) from sailors s) + (select count(b.bid)from boats b)<100) ); 

In the above example, we enforcing CHECK constraint that the number of boats and sailors should be less than 100. So here we are able to CHECK constraints of two tablets simultaneously.

2. What are Triggers ?
A trigger is a database object that is associated with the table, it will be activated when a defined action is executed for the table.The trigger can be executed when we run the following statements:



  1. INSERT
  2. UPDATE
  3. DELETE

And it can be invoked before or after the event.

Syntax –

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

Example –

create trigger t1  before  UPDATE on sailors
for each row
begin
   if new.age>60 then
      set new.age=old.age;
   else
      set new.age=new.age;
   end if;
end;
$ 

In the above example we are creating trigger before update. so, if the new age is greater than 60 we should not update else we should update.We can call this trigger by using “$” symbol.



Difference between Assertions and Triggers :

S.NoAssertionsTriggers
1.We can use Assertions when we know that the given particular condition is always true.We can use Triggers even particular condition may or may not be true.
2.When the SQL condition is not met then there are chances to an entire table or even Database to get locked up.Triggers can catch errors if the condition of the query is not true.
3.Assertions are not linked to specific table or event. It performs task specified or defined by the user.It helps in maintaining the integrity constraints in the database tables, especially when the primary key and foreign key constraint are not defined.
4.Assertions do not maintain any track of changes made in table.Triggers maintain track of all changes occurred in table.
5.Assertions have small syntax compared to Triggers.They have large Syntax to indicate each and every specific of the created trigger.
6.Modern databases do not use Assertions.Triggers are very well used in modern databses.

Assertions can’t modify the data and they are not linked to any specific tables or events in the database but Triggers are more powerful because they can check conditions and also modify the data within the tables inside a database, unlike assertions.

Attention reader! Don’t stop learning now.  Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.

Learn all GATE CS concepts with Free Live Classes on our youtube channel.

My Personal Notes arrow_drop_up
Recommended Articles
Page :