Difference between Assertions and Triggers in DBMS

In DBMS, there is always the need to make sure that data in the database is both consistent and intact. This can be done with the help of mechanisms such as assertions and triggers or any other similar one. They both encourage compliance with rules and conditions but they do it in different ways and at different times. This article focuses on decoding the major differences between assertions and triggers to let the reader know precisely when to use one in database management.

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 a situation SQL supports the creation of assertions that are constraints not associated with only one table. 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. 

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

Conclusion

Assertions and triggers are both important in managing the databases because of the following reasons. Assertions are especially useful for expressing and enforcing compound existing and future conditions across multiple tables, on the other hand, trigger are all-inclusive and are broadly used for other purposes such as automating responses to alterations to data. Comparing the described above two mechanisms makes understanding of what tool to choose for enforcing business rules and maintaining data integrity in the hands of a database administrator. 

dadimadhav

Follow

Improve

Article Tags :

• DBMS
• Difference Between
• GATE CS
• SQL
• DBMS-SQL

+1 More