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 dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html — P.Salmon
– P.Salmon, Commented Jan 10, 2019 at 9:05
when I delimiter // it gives me error " Sorry an unexpected error happened!" — Faizan Anwer Ali
– Faizan Anwer Ali, Commented Jan 10, 2019 at 9:11

P.Salmon · Accepted Answer · 2019-01-10 09:13:04Z

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, 2019 at 9:13

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Faizan Anwer Ali Over a year ago

why did you took out varible declaration?

P.Salmon Over a year ago

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 stackoverflow.com/questions/11754781/…

Faizan Anwer Ali Over a year ago

For me it is serve purpose because later I'll use in more complicated queries

Faizan Anwer Ali Over a year ago

"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?

Collectives™ on Stack Overflow

how to declare variable phpmyadmin mysql triggers properly?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related