Skip to main content

MySql Trigger set

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

Comments

Post a Comment

Popular posts from this blog

using PDO database connection add,update,delete,edit operation

PDO advantage : 1-Object Oriented 2-Bind parameters in statements (security) 3-Allows for prepared statements and rollback functionality (consistency) 4-Throws catcheable exceptions for better error handling (quality) 5-Exception mode; no need to check error state after each API call. It's best to tell PDO how you'd like the data to be fetched. You have the following options: 1-PDO::FETCH_ASSOC: returns an array indexed by column name. 2-PDO::FETCH_BOTH: (default):returns an array indexed by both column name and number. 3-PDO::FETCH_BOUND:Assigns the values of your columns to the variables set with the ->bindColumn() method. 4-PDO::FETCH_CLASS: Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist. 5-PDO::FETCH_INTO:Updates an existing instance of the named class. 6-PDO::FETCH_LAZY: Combines. 7-PDO::FETCH_BOTH/PDO:FETCH_OBJ, creating the object variable names as t...
Post a Comment
Read more

Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links

Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links user want to share any information then use following code  and read step by step Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links if you want share profile on following social link : 1-Facebook 2-twitter.com 3-LinkedIn 4-google +, Code link : https://drive.google.com/open?id=1IzTZZh_0euDqFSHL_vPRiQePlNTw3h-q Demo link : http://freeteachnology.hol.es/socialshare/ To modify a page's thumbnail image, description, and additional metadata for these services, you can provide meta tags in the HTML code of the page.Implementing Open Graph Meta Tags You can implement meta tags in a number of ways. In content management systems might  be allow you to modify a page's meta tags , then use following code in meta section of your project code, <link href="bootstrap.min.css" rel="stylesheet"> <link href="bootstrap-tour.m...
Post a Comment
Read more

GUID for globally unique identifier

How to create GUID in php 1-guid stands for globally unique identifier generally used to create random unique strings in php, create access token in php 2-Mostly use of GUID for generating access token, generate unique id, generating unique string in php. Using this article how to create guide in php you can create a random string for any use to keep unique 3-GUID consists of alphanumeric characters only and is grouped in five groups separated by hyphens as seen in this example: 3F2504E0-4F89-11D3-9A0C-0305E82C3301 Eg:- <?php /** * Generate Globally Unique Identifier (GUID) * E.g. 2EF40F5A-ADE8-5AE3-2491-85CA5CBD6EA7 * * @param boolean $include_braces Set to true if the final guid needs * to be wrapped in curly braces * @return string */ function generateGuid($include_braces = false) { if (function_exists('com_create_guid')) { if ($include_braces === true) { return com_create_guid(); } else { return substr(com_cr...
2 comments
Read more