How does SQL allow implementation of the entity integrity and referential integrity constraints

 Question: 4.3. How does SQL allow implementation of the entity integrity and referential integrity constraints described in Chapter 3? What about referential triggered actions? 

Solution: 

Entity integrity

Referential integrity

Entity integrity constraints

In the entity integrity constraint, it states that no primary key value can be NULL because. Primary key value is used to identify the individual tuples in a relation. And having NULL values for the primary key implies that we cannot identify some tuples.

Referential integrity constraints

It is specified between two relations and is used to maintain the consistency among tuples in the two relations and referential integrity constraints states that, a tuple in one relation that refers to another relation and must refer to an existing tuple in that relation.

Referential triggered actions

Schema designer can specify an alternative action to taken when a referential integrity constraint is violated by attaching a referential triggered action clause to any foreign key constraint.

Triggers in MySQL

Q: Which of the following is true of triggers in MySQL? Select all that apply.
Note: The way triggers works has changed slightly in recent versions. This question refers to the most recent version (after 6 or later).

Solution:

1.	A trigger can call a stored procedure.
2.	A trigger attached to table A that performs an operation on a table B, can cause the activation (triggering) of one or more triggers that are attached to table B.
3.	Specifying a trigger as FOR EACH ROW means the trigger is activated once for each row that is inserted, updated, or deleted.
4.	It is not possible to have multiple triggers with the same trigger event and action time (before/after).
5.	A trigger that attempts to alter the table that it is attached to will result in an error.

Solution:

Solution for 1: False, you can only execute stored procedures and Triggers in a stored procedure cannot be called directly.

Solution for 2: False, it does not create activation of several triggers at table B.
Solution for 3: True, trigger is activated when DML operations are triggered once for each row that is updated
Solution for 4: False, you can only create one trigger for an event per table as well as trigger event and action time
Solution for 5: False, unless the trigger and table posses the same name
 

Throw an error in a MySQL trigger

Question: If I have a trigger before the update on a table, how can I throw an error that prevents the update on that table?

Solution:

 As of MySQL 5.5, you can use the SIGNAL syntax to throw an exception:

signal sqlstate '45000' set message_text = 'My Error Message';

State 45000 is a generic state representing "unhandled user-defined exception".

Here is a more complete example of the approach:

delimiter //
use test//
create table trigger_test
(
    id int not null
)//
drop trigger if exists trg_trigger_test_ins //
create trigger trg_trigger_test_ins before insert on trigger_test
for each row
begin
    declare msg varchar(128);
    if new.id < 0 then
        set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
end
//

delimiter ;
-- run the following as seperate statements:
insert into trigger_test values (1), (-1), (2); -- everything fails as one row is bad
select * from trigger_test;
insert into trigger_test values (1); -- succeeds as expected
insert into trigger_test values (-1); -- fails as expected
select * from trigger_test;