CODEWITHSUNDEEP
mysqlsqltriggers
Alex Mathew
Alex Mathew

Reputation: 1554

MySQL trigger after insert and after update

I have two tables with one named att as follows

CREATE TABLE att (
  SID varchar(50) NOT NULL, 
  CID varchar(50) NOT NULL, 
  Date date NOT NULL,
  H1 varchar(1) NOT NULL DEFAULT 0,
  H2 varchar(1) NOT NULL DEFAULT 0,
  H3 varchar(1) NOT NULL DEFAULT 0,
  H4 varchar(1) NOT NULL DEFAULT 0,
  H5 varchar(1) NOT NULL DEFAULT 0,
  H6 varchar(1) NOT NULL DEFAULT 0,
  H7 varchar(1) NOT NULL DEFAULT 0,
  H8 varchar(1) NOT NULL DEFAULT 0,
  H9 varchar(1) NOT NULL DEFAULT 0,
  H10 varchar(1) NOT NULL DEFAULT 0,
  INDEX (SID, CID)
);

The other table is per with following fields:
SID CID Per

How do I write a trigger for the following:
If an update occurs in any of the fields from h1-h0 on att table then
update the per column in the per table with the following values:
((total no of 1s - total no of 0s)/(total no of 1s + total no of 0s))/100

Thanks in advance
i Developed a Trigger,but it not working,its saying error in line 11,can you say what is the problem??

create TRIGGER `att_up` AFTER UPDATE ON `attentance`
FOR EACH ROW BEGIN
  DECLARE Zeros INT;
  DECLARE Ones INT;
  DECLARE total INT;
  DECLARE atted FLOAT;
  SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)) 
      INTO Zeros FROM attentance 
      WHERE StudID=NEW.StudID;
  SELECT SUM(h1+h2+h3+h4+h5+h6+h7+h8) 
      INTO Ones FROM attentance 
      WHERE StudID=NEW.StudID;
  SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8))+ SUM(h1+h2+h3+h4+h5+h6+h7+h8) 
      INTO total FROM attentance 
      WHERE StudID=NEW.StudID;
  set atted=((ZEROS-Ones)/total)/100;
  INSERT into per(per) values (atted);
END$$

Upvotes: 2

Views: 16617

Answers (2)

Joseadrian
Joseadrian

Reputation: 4374

DELIMITER $$

CREATE TRIGGER `att_up` AFTER UPDATE ON `attentance`
FOR EACH ROW

BEGIN

SET @zeros = 0;
SET @ones = 0;
SET @total = 0;
SET @atted = 0;


(SELECT (8-SUM(h1)+SUM(h2)+SUM(h3)+SUM(h4)+SUM(h5)+SUM(h6)+SUM(h7)+SUM(h8)) INTO @zeros FROM attentance WHERE StudID=NEW.StudID);
(SELECT SUM(h1+h2+h3+h4+h5+h6+h7+h8) INTO @ones FROM attentance WHERE StudID=NEW.StudID);
(SELECT (SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8))+ SUM(h1+h2+h3+h4+h5+h6+h7+h8)) INTO @total FROM attentance WHERE StudID=NEW.StudID);

SELECT (((@zeros-@ones)/@total)/100) INTO @atted FROM (SELECT 1) AS x;

END$$

Upvotes: -1

outis
outis

Reputation: 77400

Make sure you change the delimiter before defining the trigger. Also make sure you're using the same table and column names when you create the table and the trigger (you're using att and attendance, and SID and StudID, in your examples).

As it is, the trigger definition caused no error when I tested it in MySQL 5.1.55 after setting the delimiter.

delimiter $$
CREATE TRIGGER `att_up` 
  AFTER UPDATE ON `attendance`
FOR EACH ROW 
BEGIN
  DECLARE Zeros INT;
  DECLARE Ones INT;
  DECLARE total INT;
  DECLARE attend FLOAT;
  SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)), 
         SUM(h1+h2+h3+h4+h5+h6+h7+h8),
         SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)) + SUM(h1+h2+h3+h4+h5+h6+h7+h8)
    INTO Zeros, Ones, Total FROM attendance 
    WHERE SID=NEW.SID;
  SET attend=((Zeros-Ones)/total)/100;
  INSERT INTO per (SID, CID, per) values (NEW.SID, NEW.CID, attend)
    ON DUPLICATE KEY UPDATE per=attend;
END$$
delimiter ;

Upvotes: 3

Related Questions