Free Teachnology provide free #PHP,#MySql,#Javascript,#jQuery,#API integration,#Payment gateway integration,#Social Media login
In this section, you will learn how to work with the MySQL triggers. By definition, a trigger or database trigger is a stored program executed automatically to respond to a specific event e.g., insert, update or delete occurred in a table.
The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.
Uses for triggers:
Enforce business rules
Validate input data
Generate a unique value for a newly-inserted row in a different file.
Write to other files for audit trail purposes
Query from other files for cross-referencing purposes
Following table of create trigger :
1-Create table :
when create trigger at that time first you can create table following table is create table :
Code :
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2018 at 01:22 PM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `triggerExample`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees`
--
CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
`changedat` datetime DEFAULT NULL,
`action` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employees`
--
INSERT INTO `employees` (`id`, `employeeNumber`, `lastname`, `changedat`, `action`) VALUES
(1, 120, 'disuza', '2018-06-12 00:00:00', NULL),
(2, 120, 'jen', '2018-06-12 00:00:00', NULL);
--
-- Triggers `employees`
--
DELIMITER $$
CREATE TRIGGER `before_employee_update` BEFORE UPDATE ON `employees` FOR EACH ROW BEGIN
INSERT INTO employees_audit
SET action = 'updte',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `insert records` AFTER INSERT ON `employees` FOR EACH ROW INSERT INTO employees_test
( employeeNumber,
lastname,
changedat)
VALUES
( NEW.employeeNumber,
NEW.lastname,
NOW())
$$
DELIMITER ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
above table is emplyee create that employee number,employee last name,create or change date and action
2-Insert Trigger :
You put the trigger name after the CREATE TRIGGER statement. The trigger name should follow the naming convention [trigger time]_[table name]_[trigger event], for example before_employees_update.
Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger. You use the BEFORE keyword if you want to process action prior to the change is made on the table and AFTER if you need to process action after the change is made.
when insert trigger genrate at employee record insert into database, following trigger point set in mysql trigger section :
Time : AFTER
EVENT : INSERT
Code :
INSERT INTO employees_test
( employeeNumber,
lastname,
changedat)
VALUES
( NEW.employeeNumber,
NEW.lastname,
NOW())
when set insert after tigger employee record insert into database at time employee number and employee,lastname inert into employee test table
employee_test table create code :
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2018 at 01:27 PM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `triggerExample`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees_test`
--
CREATE TABLE `employees_test` (
`id` int(11) NOT NULL,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
`changedat` datetime DEFAULT NULL,
`action` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employees_test`
--
INSERT INTO `employees_test` (`id`, `employeeNumber`, `lastname`, `changedat`, `action`) VALUES
(2, 120, 'checking', '2018-06-12 11:57:07', NULL);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employees_test`
--
ALTER TABLE `employees_test`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employees_test`
--
ALTER TABLE `employees_test`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
2-UPDATE Trigger :
Update trigger fire when records update update from another table :
following code use for create new table
Code :
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2018 at 02:58 PM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `triggerExample`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees_audit`
--
CREATE TABLE `employees_audit` (
`id` int(11) NOT NULL,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
`changedat` datetime DEFAULT NULL,
`action` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employees_audit`
--
ALTER TABLE `employees_audit`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employees_audit`
--
ALTER TABLE `employees_audit`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
above table create audit table and set trigger on table :
Code :
Time : BEOFRE
Event : UPDTE
BEGIN
INSERT INTO employees_audit
SET action = 'updte',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
END
same as you can create delete trigger , hope that above tutorial understand better if you have any query please email on : disuzajen@gmail.com
In this section, you will learn how to work with the MySQL triggers. By definition, a trigger or database trigger is a stored program executed automatically to respond to a specific event e.g., insert, update or delete occurred in a table.
The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.
Uses for triggers:
Enforce business rules
Validate input data
Generate a unique value for a newly-inserted row in a different file.
Write to other files for audit trail purposes
Query from other files for cross-referencing purposes
Following table of create trigger :
1-Create table :
when create trigger at that time first you can create table following table is create table :
Code :
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2018 at 01:22 PM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `triggerExample`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees`
--
CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
`changedat` datetime DEFAULT NULL,
`action` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employees`
--
INSERT INTO `employees` (`id`, `employeeNumber`, `lastname`, `changedat`, `action`) VALUES
(1, 120, 'disuza', '2018-06-12 00:00:00', NULL),
(2, 120, 'jen', '2018-06-12 00:00:00', NULL);
--
-- Triggers `employees`
--
DELIMITER $$
CREATE TRIGGER `before_employee_update` BEFORE UPDATE ON `employees` FOR EACH ROW BEGIN
INSERT INTO employees_audit
SET action = 'updte',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `insert records` AFTER INSERT ON `employees` FOR EACH ROW INSERT INTO employees_test
( employeeNumber,
lastname,
changedat)
VALUES
( NEW.employeeNumber,
NEW.lastname,
NOW())
$$
DELIMITER ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
above table is emplyee create that employee number,employee last name,create or change date and action
2-Insert Trigger :
You put the trigger name after the CREATE TRIGGER statement. The trigger name should follow the naming convention [trigger time]_[table name]_[trigger event], for example before_employees_update.
Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger. You use the BEFORE keyword if you want to process action prior to the change is made on the table and AFTER if you need to process action after the change is made.
when insert trigger genrate at employee record insert into database, following trigger point set in mysql trigger section :
Time : AFTER
EVENT : INSERT
Code :
INSERT INTO employees_test
( employeeNumber,
lastname,
changedat)
VALUES
( NEW.employeeNumber,
NEW.lastname,
NOW())
when set insert after tigger employee record insert into database at time employee number and employee,lastname inert into employee test table
employee_test table create code :
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2018 at 01:27 PM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `triggerExample`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees_test`
--
CREATE TABLE `employees_test` (
`id` int(11) NOT NULL,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
`changedat` datetime DEFAULT NULL,
`action` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employees_test`
--
INSERT INTO `employees_test` (`id`, `employeeNumber`, `lastname`, `changedat`, `action`) VALUES
(2, 120, 'checking', '2018-06-12 11:57:07', NULL);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employees_test`
--
ALTER TABLE `employees_test`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employees_test`
--
ALTER TABLE `employees_test`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
2-UPDATE Trigger :
Update trigger fire when records update update from another table :
following code use for create new table
Code :
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2018 at 02:58 PM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.20
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `triggerExample`
--
-- --------------------------------------------------------
--
-- Table structure for table `employees_audit`
--
CREATE TABLE `employees_audit` (
`id` int(11) NOT NULL,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
`changedat` datetime DEFAULT NULL,
`action` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employees_audit`
--
ALTER TABLE `employees_audit`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employees_audit`
--
ALTER TABLE `employees_audit`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
above table create audit table and set trigger on table :
Code :
Time : BEOFRE
Event : UPDTE
BEGIN
INSERT INTO employees_audit
SET action = 'updte',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
END
same as you can create delete trigger , hope that above tutorial understand better if you have any query please email on : disuzajen@gmail.com
Comments