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 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.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 databases.
7.	Purpose of assertions  is to Enforces business rules and constraints.	Purpose of triggers is to Executes actions in response to data changes.
8.	Activation is checked after a transaction completes	Activation is activated by data changes during a transaction
9.	Granularity applies to the entire database	Granularity applies to a specific table or view
10.	Syntax Uses SQL statements	Syntax Uses procedural code (e.g. PL/SQL, T-SQL)
11.	Error handling Causes transaction to be rolled back.	Error handling can ignore errors or handle them explicitly
12.	Assertions may slow down performance of queries.	Triggers Can impact performance of data changes.
13.	Assertions are Easy to debug with SQL statements.	Triggers are more difficult to debug procedural code
14.	Examples- CHECK constraints, FOREIGN KEY constraints	Examples – AFTER INSERT triggers, INSTEAD OF triggers

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.