Skip to content
Related Articles

Related Articles

Difference between Assertions and Triggers in DBMS

View Discussion
Improve Article
Save Article
  • Difficulty Level : Medium
  • Last Updated : 21 Sep, 2021
View Discussion
Improve Article
Save Article

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 that 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 triggers before updates. 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 databases.

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. 

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!