Triggers in MySQL

-

Trigger MySQL: Trigger can be used in two ways with before and after with Delete ,insert and update command .Let me show you a example of before update  . I have created two table employee and employee_audit . employee table is table where my raw data will be stored and employee_audit table is table where to store data after changes or can say trigger execution .

Table structure of employee and employee_audit table :



CREATE TABLE  `test`.`employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `lstname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;




CREATE TABLE  `test`.`employee_audit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `lstname` varchar(20) DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `upd_datetime` datetime DEFAULT NULL,
  `newname` varchar(20) DEFAULT NULL,
  `newlstname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;




Trigger Creation :

 DELIMITER $$
CREATE TRIGGER before_employee_update2
    BEFORE UPDATE ON employee
    FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    SET action = 'update',
     name = OLD.name,
     lstname = OLD.lstname,
     upd_datetime = now(),
     newname = NEW.name ,
     newlstname = new.lstname ;
END$$
DELIMITER ;


Let us suppose ,we have insert data into employee table :






So before execute any update ,we will see ,first employee_audit table data .





Now I am executing a command update on name Gaurav :
update employee set name = 'guddu' where name = 'gaurav' ;




Check name has been changed ,now  we will see our employee_audit table content :






So you can ,what ever ever ,we have defined in trigger ,accordingly things are executing and updateded employee_audit table.

You can see trigger detail by just executing command “show triggers”. 

















Comments

Post a Comment

Popular posts from this blog

MySQL 8.0.2 Windows Function

-
Q : What is sql windows function  ? Ans  : a windows sql function will perform aggregation for each row in result set  , letting  each row in  the result set , leatting each row retain its identity . Example  : mysql> CREATE TABLE t(i INT); mysql> INSERT INTO t VALUES (1),(2),(3),(4); mysql> SELECT SUM(i) AS sum FROM t; +------+ | sum  | +------+ |   10 | +------+ mysql> SELECT i, SUM(i) OVER () AS sum FROM t; +------+------+ | i    | sum  | +------+------+ |    1 |   10 | |    2 |   10 | |    3 |   10 | |    4 |   10 | So  you can see only by  using over()  windows function  , sum is showing along with  each value that  has been summed . Q: what are types of windows sql function  ? Ans :  sql aggregate function can be used in ...
Read more

list in python

-
# declaring a list list_t = [ "my" , "name" , "is" , "abhishek" ] print (list_t) # accessing first list item print (list_t[ 0 ]) # accessing all item of list for x in list_t: print (x) # inserting item at position in list . list_t.insert( 0 , "hello" ) print (list_t) # extending a list lastname = [ "rathor" ] list_t.extend(lastname) print (list_t) # removing item from list list_t.pop( 0 ) print (list_t) list_t.remove( "rathor" ) print (list_t) # index of item in list print (list_t.index( 'name' )) # sorting an index list_t.sort() print (list_t) # reverse is also possible in index list_t.reverse() print (list_t) # constructing a list with constructor list2 = list (( "abhishek" , "pratap" , "singh" , "rathor" )) print (list2) # tuple and list both behave same except tuple are unchangeable and ordered collection # so inserting and removing ...
Read more