how to declare variable phpmyadmin mysql triggers properly?

Question

I have a database where whenever residential address update in user table I want it to store in history table of user. For that I'm trying to write triggers but failing miserably in phpmyadmin. Also it's not giving me proper reason why so I can correct it. This is what I have done so far.

DROP TRIGGER IF EXISTS `record_history`;
CREATE TRIGGER `record_history` AFTER UPDATE ON `s_user` 
FOR EACH ROW
BEGIN
    DECLARE date_current datetime;
    DECLARE residential_address varchar(1000);
    SET @date_current = NOW();
    SET @residential_address = NEW.residential_address;
    IF (@residential_address <> OLD.residential_address AND @residential_address != "" AND @residential_address IS NOT NULL) THEN
        INSERT INTO history_residential_address (`s_u_id`, `residential_address`, `status`, `date_added`, `date_updated`) VALUES 
        (OLD.s_u_id, @residential_address, 1, @date_current, @date_current);
    END IF;
END;
delimiter ;

The end statement that matchs the begin is not terminated.And perhaps you are not setting delimiters - please review https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html — P.Salmon, Jan 10 '19 at 09:05
when I delimiter // it gives me error " Sorry an unexpected error happened!" — Faizan Anwer Ali Rupani, Jan 10 '19 at 09:11

score 2 · Accepted Answer · answered Jan 10 '19 at 09:13

2

A cleaner version of your code

DROP TRIGGER IF EXISTS `record_history`;

delimiter $$
CREATE TRIGGER `record_history` AFTER UPDATE ON `s_user` 
FOR EACH ROW
BEGIN
    IF (new.residential_address <> OLD.residential_address AND new.residential_address <> "" AND new.residential_address IS NOT NULL) THEN
        INSERT INTO history_residential_address (`s_u_id`, `residential_address`, `status`, `date_added`, `date_updated`) VALUES 
        (OLD.s_u_id, new.residential_address, 1, now(), now());
    END IF;
END $$
delimiter ;

If you are still having problems please add sample data from s_user as text to the question.

answered Jan 10 '19 at 09:13

P.Salmon

17,104
2
12
19

why did you took out varible declaration? – Faizan Anwer Ali Rupani Jan 10 '19 at 09:20
They served no purpose that i could see and the declared variables were not being used at all.Also user defined variables and local variables are not the same thing https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – P.Salmon Jan 10 '19 at 09:24
For me it is serve purpose because later I'll use in more complicated queries – Faizan Anwer Ali Rupani Jan 10 '19 at 09:25
"update `s_user` SET status = 0;" also trigger this `record_history`. And NEW keyword also have residential_address property with previous value. So I have multiple entries whenever I update user table. How do I fix it? – Faizan Anwer Ali Rupani Jan 10 '19 at 09:57

how to declare variable phpmyadmin mysql triggers properly?

1 Answers1