CODEWITHSUNDEEP
mysqltriggersconditional-statements
Apostolos Traganis
Apostolos Traganis

Reputation: 81

mysql trigger error with 2 conditions

I want to add an after insert trigger which will do the following.

The first IF condition works normally, but when it comes to the second everything stops.

Any ideas?

USE `Syslog`;
DELIMITER $$

CREATE TRIGGER `SystemEventsR_AINS` AFTER INSERT ON SystemEventsR FOR EACH ROW

IF 
(exists
(select syslogtag from SystemEventsRcounter where syslogtag=
new.syslogtag) 

AND

(select simpledate from SystemEventsRcounter
where syslogtag=new.syslogtag)=new.simpledate) 

THEN
UPDATE SystemEventsRcounter 
SET records=records+1
WHERE SystemEventsRcounter.syslogtag=new.syslogtag;

ELSE INSERT SystemEventsRcounter (simpledate, syslogtag, records) values (new.simpledate,new.syslogtag,1);
END IF

Upvotes: 1

Views: 260

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED:

What you need is INSERT INTO ... ON DUPLICATE KEY.

CREATE TRIGGER `SystemEventsR_AINS` 
AFTER INSERT ON SystemEventsR 
FOR EACH ROW
    INSERT INTO SystemEventsRcounter (simpledate, syslogtag, records) 
    VALUES (NEW.simpledate, NEW.syslogtag, 1)
    ON DUPLICATE KEY UPDATE records = records + 1;

In order for it to work you need to create a unique composite index on (simpledate, syslogtag)

CREATE UNIQUE INDEX idx_u_simpledate_syslogtag
ON SystemEventsRcounter (simpledate, syslogtag);

Here is SQLFiddle demo.

If you wanted it your way then it might look like 

DELIMITER $$
CREATE TRIGGER `SystemEventsR_AINS` 
AFTER INSERT ON SystemEventsR 
FOR EACH ROW
BEGIN
    IF ( 
          SELECT COUNT(*) simpledate 
            FROM SystemEventsRcounter 
           WHERE syslogtag  = NEW.syslogtag
             AND simpledate = NEW.simpledate
       ) > 0 THEN
        UPDATE SystemEventsRcounter 
           SET records = records + 1
        WHERE SystemEventsRcounter.syslogtag = NEW.syslogtag;
    ELSE 
        INSERT INTO SystemEventsRcounter (simpledate, syslogtag, records) 
        VALUES (NEW.simpledate, NEW.syslogtag, 1);
    END IF;
END$$
DELIMITER ;

Here is SQLFiddle demo.

Upvotes: 1

Related Questions