Creating triggers:
Before insert trigger:
To Create before insert trigger to make sure any new value of Working hours, if it is negative, then it should be inserted as positive.
— Create the table
create table emp_bit(
name varchar(55),
occupation varchar (100),
working_date date,
working_hours int
);
— Insert 6 records into the table
insert into emp_bit values
(‘Robin’,’scientist’,’2020-10-04′,12),
(‘warner’,’engineer’,’2020-10-04′,10),
(‘Peter’,’Actor’,’2020-10-04′,13),
(‘marco’,’doctor’,’2020-10-04′,14),
(‘brayden’,’teacher’,’2020-10-04′,12),
(‘Antonio’, ‘Business’,’2020-10-04′,11);
To show how the table is created using different commands:
show create table emp_bit;
Before insert trigger:
Before inserting a new row into the emp_bit table a trigger named BIT_emp1 is to be executed. The trigger is defined to perform an action on each row being inserted.
The trigger’s logic is as follows:
If the value of the working_hours column in the new row being inserted is less than 0, then the trigger will execute the following action:
Set the value of the working_hours column in the new row to its absolute value (i.e., negate the negative value).
The delimiter // statement is used to change the delimiter temporarily to // instead of the default ;. This is necessary because the trigger definition contains multiple statements.
delimiter //
create trigger BIT_emp1
before insert
on emp_bit for each row
begin
if new.working_hours<0 then
set new.working_hours = - new.working_hours;
end if;
end //Before insert trigger:
INSERT INTO emp_bit
VALUES ("kedar", "lawyer", '2020-10-04', -8);After the insertion, the trigger will be triggered and the value of working_hours will be changed to its absolute value (8) in the inserted row.
After insert trigger:
Referential integrity and cardinality:
Before delete trigger is like taking a backup:
Common application:
To find the attrition rate of an organization in a year, month.
On delete cascade:
When a foreign key relationship is defined between two tables, specifying ON DELETE CASCADE means that when a row in the referenced (parent) table is deleted, all related rows in the referencing (child) table will also be deleted automatically.
To achieve this functionality using triggers, we can create an AFTER DELETE trigger on the parent table. Inside the trigger, we can delete the corresponding rows from the child table.
Table with one foreign key:
create table books(
bookid integer primary key,
title varchar(255),
aid integer,
Foreign KEY(aid) references authors(authorid)
ON delete cascade
ON update cascade
);Table with two foreign key:
create table publisher(
bookid integer primary key,
title varchar(255),
aid integer,
publid integer,
Foreign KEY(aid) references authors(authorId) ON delete cascade,
Foreign KEY(pubid) references publisher(pid)
ON delete cascade
ON update cascade);After delete trigger:
In case of using unique column or primary key in where clause, changing sql_safe_update mode is not required.
Explanation: After deleting a record from the employees table create a trigger to update budget table.
Signal processing in database:
Before update trigger:
ON UPDATE CASCADE:
When a foreign key relationship is defined between two tables, specifying ON UPDATE CASCADE means that when a value in the referenced (parent) table is updated, all related values in the referencing (child) table will also be updated automatically.
To achieve this functionality using triggers, we can create an AFTER UPDATE trigger on the parent table. Inside the trigger, we can update the corresponding values in the child table.
It’s important to note that using triggers to mimic the behavior of ON DELETE CASCADE and ON UPDATE CASCADE can be more complex and error-prone compared to using the built-in functionality provided by the database engine.
It’s generally recommended to use the native ON DELETE CASCADE and ON UPDATE CASCADE options when defining foreign key relationships, as they are specifically designed to handle these scenarios efficiently and reliably.
Note: Instead of on delete cascade use on delete set null/ on update set null.
After Update trigger:
To check before updating:
Updating a table:
Explanation:
Scenario to solve using before insert:
The tables movies and rentals are created without primary or foreign keys. Instead, triggers are used to enforce the desired functionality.
The 'movies_release_date_trigger‘ trigger is created on the movies table and checks if the release date of a movie being inserted is in the future. If it is, an error is raised using the SIGNAL statement.
The 'rentals_return_date_trigger‘ trigger is created on the rentals table and automatically updates the return date to the current date when a rental is marked as returned.
Using primary and foreign keys is generally considered a best practice for maintaining data integrity and ensuring efficient query execution.
CREATE TABLE movies (
movie_id INT,
title VARCHAR(255),
genre VARCHAR(255),
release_date DATE
);
CREATE TABLE rentals (
rental_id INT,
movie_id INT,
rental_date DATE,
return_date DATE,
customer_id INT
);
DELIMITER //
CREATE TRIGGER movies_release_date_trigger
BEFORE INSERT ON movies
FOR EACH ROW
BEGIN
IF NEW.release_date > CURDATE() THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot insert movies with future release dates';
END IF;
END //
CREATE TRIGGER rentals_return_date_trigger
BEFORE UPDATE ON rentals
FOR EACH ROW
BEGIN
IF NEW.return_date IS NOT NULL THEN
SET NEW.return_date = CURDATE();
END IF;
END //
DELIMITER ;
Recent Comments