Difference between Assertions and Triggers in DBMS

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.No Assertions Triggers
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. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.