在我的sql中调用触发器中的函数

function

delimiter $$
    create function my_calculation( s1_ia_att decimal, s1_t1 decimal, s1_t2 decimal, s1_t3 decimal, s2_ia_att decimal, s2_t1 decimal, s2_t2 decimal, s2_t3 decimal, s3_ia_att decimal, s3_t1 decimal, s3_t2 decimal, s3_t3 decimal, s4_ia_att decimal, s4_t1 decimal, s4_t2 decimal, s4_t3 decimal, s5_ia_att decimal, s5_t1 decimal, s5_t2 decimal, s5_t3 decimal, s6_ia_att decimal, s6_t1 decimal, s6_t2 decimal, s6_t3 decimal, s7_ia_att decimal, s7_t1 decimal, s7_t2 decimal, s7_t3 decimal, s8_ia_att decimal, s8_t1 decimal, s8_t2 decimal, s8_t3 decimal )
    returns int
    deterministic 
    begin 
      RETURN
      (s1_t1 + s1_t2 + s1_t3 - LEAST(s1_t1, s1_t2, s1_t3)) / 2
      +
      CASE 
        WHEN s1_ia_att > 95 THEN 5
        WHEN s1_ia_att BETWEEN 81 AND 95 THEN 4
        WHEN s1_ia_att BETWEEN 76 AND 80 THEN 3
        WHEN s1_ia_att BETWEEN 61 AND 75 THEN 2
        WHEN s1_ia_att < 60 THEN 0 

       (s2_t1 + s2_t2 + s2_t3 - LEAST(s2_t1, s2_t2, s2_t3)) / 2
      +
      CASE 
        WHEN s2_ia_att > 95 THEN 5
        WHEN s2_ia_att BETWEEN 81 AND 95 THEN 4
        WHEN s2_ia_att BETWEEN 76 AND 80 THEN 3
        WHEN s2_ia_att BETWEEN 61 AND 75 THEN 2
        WHEN s2_ia_att < 60 THEN 0 
       END;
    END $$
    DELIMITER ;

And here goes my trigger

这就是我的触发器

delimiter $$
create trigger abc before insert 
on `test` for each row
begin
   declare `value` decimal;

   SET NEW.s1_ia_tot = my_calculation( NEW.s1_ia_att, NEW.s1_t1, NEW.s1_t2, NEW.s1_t3 );
   SET NEW.s2_ia_tot = my_calculation( NEW.s2_ia_att, NEW.s2_t1, NEW.s2_t2, NEW.s2_t3 );
   SET NEW.s3_ia_tot = my_calculation( NEW.s3_ia_att, NEW.s3_t1, NEW.s3_t2, NEW.s3_t3 );
   ......
end $$
delimiter ;

I have paced my trigger and the function and nothing happening the ia_att_tot field is still null only I am not able to find what is the problem in this code. Can someone tell what's wrong in my code?

我已经调整了我的触发器和函数,并且ia_att_tot字段中没有发生任何事情仍然为空我只是无法找到此代码中的问题。有人可以告诉我的代码有什么问题吗?

My table

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s1_ia_att` DECIMAL(12, 2) NULL,
....
....
`s1_t1` DECIMAL(12, 2) NULL,
`s1_t2` DECIMAL(12, 2) NULL,
`s1_t3` DECIMAL(12, 2) NULL,
....
....
`s1_ia_tot` DECIMAL(12, 2) NULL,
 ......
 .....
PRIMARY KEY (`id`)
);

1 个解决方案

#1

Possibly you are passing values that are out of range to your CASE statement in the function. As there is no ELSE clause in the case statement, it returns NULL by default. Define an ELSE clause and set a non-NULL value. Then see what happens.

您可能正在将超出范围的值传递给函数中的CASE语句。由于case语句中没有ELSE子句,因此默认情况下返回NULL。定义ELSE子句并设置非NULL值。然后看看会发生什么。

1 个解决方案

#1

更多相关文章

随机推荐