Explain Assertion and Triggers with example.

Question

Bipin Tiwari · Accepted Answer

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. 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: INSERT UPDATE 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.

Explain Assertion and Triggers with example.

Leave your Answer:

Hits Counter

Question

Explain Assertion and Triggers with example.

Leave your Answer: