/******************************************************************************
  2 Examples of triggers
******************************************************************************/
/*====================================================================
  Creating a BEFORE trigger to validate incoming data 
=====================================================================*/
DROP TRIGGER payment_trig;
/
CREATE TRIGGER payment_trig BEFORE UPDATE ON payment FOR EACH ROW
    BEGIN
    IF NEW.amount > OLD.amount * 1.05 THEN
        INSERT INTO output 
            VALUES('New amount cannot exceed old amount + 5%');
        SELECT 'Force error' INTO @error FROM dummy_table;       --invalid table
    END IF;
END;
/

UPDATE payment 
   SET amount=amount*1.09;
/




/*====================================================================
  Creating an AFTER trigger to log user activities 
=====================================================================*/
DROP TRIGGER course_trig;
/
CREATE TRIGGER course_trig AFTER UPDATE ON course FOR EACH ROW
BEGIN
    INSERT INTO output 
        VALUES(concat(NOW(),' ',USER(),
             ' changed: price ',OLD.price,' to: ',NEW.price));

    IF (OLD.description != NEW.description) THEN            --only if different
        INSERT INTO output 
            VALUES(concat(NOW(),' ',USER(),
                 ' changed: desc  ',OLD.description,' to: ',NEW.description));
    END IF;
END;
/

UPDATE course
   SET  price       =  1500,
        description = 'Introduction to Java'
 WHERE course_id='X52-9238';
/

SELECT * from output;