CODEWITHSUNDEEP
mysqltriggers
malloc
malloc

Reputation: 313

creating triggers in mysql

I've some problem doing a trigger.. I've two tables: table1 and table2 in the table2 I've the foreign key of table1 , also in the table1 I've a date... What I've to do is: subtract the current date with the date in table1 and if is major of 7 then I must set to "10" the value in an attribute in table2 if is minor then I must set "20".. So I tried this:

CREATE DEFINER = CURRENT_USER TRIGGER `datab1`.`table2_AFTER_INSERT` AFTER INSERT ON `table2` FOR EACH ROW
BEGIN
    if(year(currentdate())-year(table1.datein))<7 then
set table2.test="10"
elseif (year(currentdate())-year(table1.datein))>=7 then
set table2.test="20"
end if;

END

but I get a lot of errors.. I've started to learn this stuff shortly and I don't have understand very well how to use the triggers.. can you help me?

Upvotes: 1

Views: 76

Answers (1)

Tata
Tata

Reputation: 809

In triggers in MySQL - you have to write SQL. MySQL does not know what you mean by year(table1.datein) for example.

If I understand correctly - you are trying to update a field of the inserted row based on the condition from table1.

Also in order to change the inserted row you should define a BEFORE trigger and not AFTER.

Here is how that should be done:

CREATE DEFINER = CURRENT_USER TRIGGER  `datab1`.`table2_BEFORE_INSERT`
BEFORE INSERT ON `table2` FOR EACH ROW
BEGIN
    DECLARE x INT;
    SET x= (SELECT year(NOW()) - year(table1.datein) FROM table1 WHERE NEW.fk_field = table1.id );
    IF x <7 THEN
        SET NEW.test = 10;
    ELSE
        SET NEW.test = 20;
    END IF;
END;

Hope that helps.

Upvotes: 1

Related Questions