TRIGGER IN MYSQL

 CREATE TABLE  1 :

 

MYSQL>CREATE TABLE  STATION  (ID int , STATION_NAME VARCHAR(20),UPDATE_USER VARCHAR(20));

 

 

CREATE TABLE  2 :

 

MYSQL> CREATE TABLE AUDIT (ID INT ,UPDATE_BY_USER VARCHAR (20))  ;

If you want to see all trigger 

 

MYSQL> SHOW TRIGGERS;

 

MYSQL> SHOW CREATE TRIGGER   TRIGGER_NAME

INSERT TRIGGER :

 

           MYSQL> DELIMITER $$

            CREATE TRIGGER INS_DATA BEFORE INSERT ON STATION 

            FOR EACH ROW 

            begin 

            INSERT INTO AUDIT  (ID,UPDATE_BY_USER )

            VALUES  ( NEW.ID ,NEW.UPDATE_BY_USER ) 

            END $$

 

           DELIMITER ;

MYSQL>INSER INTO  STATION VALUE (1,'STR_AGRA','ROBBIN') ;

MYSQL>SELECT *  FROM STATION ;

MYSQL>SELECT *  FROM AUDIT ;

UPDATE TRIGGER :

 

           MYSQL> DELIMITER $$

            CREATE TRIGGER INS_DATA BEFORE UPDATE ON STATION 

            FOR EACH ROW 

            begin 

            INSERT INTO AUDIT  (ID,UPDATE_BY_USER )

            VALUES  ( NEW.ID ,NEW.UPDATE_BY_USER ) 

            END $$

 

           DELIMITER ;

MYSQL>UPDATE  STATION SET STATION_NAME='STON_BAL' WHERE ID=1 ;

MYSQL>SELECT *  FROM STATION ;

MYSQL>SELECT *  FROM AUDIT ;

DELETE TRIGGER :

 

MYSQL> DELIMITER $$

            CREATE TRIGGER INS_DATA BEFORE DELTE ON STATION 

            FOR EACH ROW 

            begin 

            INSERT INTO AUDIT  (ID,UPDATE_BY_USER )

            VALUES  ( NEW.ID ,NEW.UPDATE_BY_USER ) 

            END $$

 

           DELIMITER ;

MYSQL>DELETE  FROM STATION WHERE ID=1 ;

MYSQL>SELECT *  FROM AUDIT ;

YOU CAN DROP TRIGGER :   DROP TRIGGER  TRIGGER_NAME ;

 

 

create table employee (id int,emp_name varchar(10));
 

insert into employee values (1,'A'),(2,'B'),(3,'C');
 

select * from employee;
 

select * from  empl_audit ;
 

update employee set emp_name='K' where id=1;
DELIMITER $$
create trigger before_update
before update on employee
for each row
begin
if  new.emp_name <> old.emp_name then
insert into empl_audit (id,emp_name) values (old.id,NEW.emp_name);
else
insert into empl_audit (id,emp_name) values (old.id,'NO');
end if;
END$$   
DELIMITER ;