开发者

mysql error on trigger creation with extract function

开发者 https://www.devze.com 2023-01-14 06:41 出处:网络
When I try create a trigger as given below, CREATE TRIGGER FiscalYearTable1_bi BEFORE INSERT ON FiscalYearTable1

When I try create a trigger as given below,

CREATE TRIGGER FiscalYearTable1_bi 
BEFORE INSERT  
ON FiscalYearTable1 
FOR EACH ROW  
     IF ( 
             ( EXTRACT (YEAR FROM FiscalYearTable1.start_date) !=  FiscalYearTable1.fiscal_year - 1) OR
            (EXTRACT (MONTH FROM FiscalYearTable1.start_date) != 04) OR
            (EXTRACT (DAY FROM FiscalYearTable1.start_date) != 01) 
       ) 
          SET FiscalYearTable1.fiscal_year = 1/0;

I get following error,

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 开发者_StackOverflowuse near 'FROM FiscalYearTable1.start_date) != FiscalYearTable1.fiscal_year - 1) OR (EXTRA' at line 1

I can't figure out what the error is. Any ideas? Thanks


something like this:

delimiter #

create trigger FiscalYearTable1_before_ins_trig before insert on FiscalYearTable1
for each row
begin

declare y smallint unsigned default 0;
declare m tinyint unsigned default 0;
declare d tinyint unsigned default 0;

  set y = year(new.start_date);
  set m = month(new.start_date);
  set d = day(new.start_date);

  -- whatever logic you require...
  if y != new.fiscal_year-1 or m != 4 or d != 1 then
    set new.fiscal_year = null; 
  end if;

end#

delimiter ;
0

精彩评论

暂无评论...
验证码 换一张
取 消