日期:2014-05-17  浏览次数:20762 次

~~~~~~~~一个完整的触发器例子,需要高手帮忙改错~~~~帮帮忙啊~~~~~~
我写的触发器如下,但提示有错误,错误提示为:

PLS-00561:     字元集参数   'RIGHT '的值不符!


CREATE   OR   REPLACE   TRIGGER   DBAUSER.TUPDATE_TSURPLUS
BEFORE   DELETE   OR   INSERT   OR   UPDATE
ON   DBAUSER.T_OVERTIME  
REFERENCING   NEW   AS   NEW   OLD   AS   OLD
FOR   EACH   ROW
DECLARE
TMPVAR1   NUMBER;
TMPVAR2   NUMBER;
BEGIN
      TMPVAR1:=0;
      TMPVAR2:=0;
    IF   INSERTING   THEN
      SELECT   COUNT(*)   INTO   TMPVAR1   FROM   T_TOTALSURPLUS   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;  
  IF   TMPVAR1> 0   THEN
        UPDATE   T_TOTALSURPLUS   SET   T_TOTALSURPLUS.SRPLS_HR=(T_TOTALSURPLUS.SRPLS_HR+:NEW.AHR)   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
ELSIF   TMPVAR1=0   THEN
        INSERT   INTO   T_TOTALSURPLUS(IDS,VRTM_SORT,SRPLS_HR)   VALUES(:NEW.IDS,:NEW.VRTMSORT,:NEW.AHR);
END   IF;
    ELSIF   UPDATING   THEN
                  IF   :NEW.VRTMSORT=:OLD.VRTMSORT   THEN
      IF   :NEW.AHR <> :OLD.AHR   THEN
              UPDATE   T_TOTALSURPLUS   SET   T_TOTALSURPLUS.SRPLS_HR=(T_TOTALSURPLUS.SRPLS_HR-:OLD.AHR+:NEW.AHR)   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
  END   IF;
    ELSIF   :NEW.VRTMSORT <> :OLD.VRTMSORT   THEN
        SELECT   COUNT(*)   INTO   TMPVAR2   FROM   T_TOTALSURPLUS   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
IF   TMPVAR2> 0   THEN
        UPDATE   T_TOTALSURPLUS   SET   T_TOTALSURPLUS.SRPLS_HR=(T_TOTALSURPLUS.SRPLS_HR-:NEW.AHR)   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:OLD.VRTMSORT;
UPDATE   T_TOTALSURPLUS   SET   T_TOTALSURPLUS.SRPLS_HR=(T_TOTALSURPLUS.SRPLS_HR+:NEW.AHR)   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
ELSIF   TMPVAR2=0   THEN
      UPDATE   T_TOTALSURPLUS   SET   T_TOTALSURPLUS.SRPLS_HR=(T_TOTALSURPLUS.SRPLS_HR-:NEW.AHR)   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:OLD.VRTMSORT;
    INSERT   INTO   T_TOTALSURPLUS(IDS,VRTM_SORT,SRPLS_HR)   VALUES(:NEW.IDS,NEW.VRTMSORT,:NEW.AHR);  
END   IF;
          END   IF;  
    ELSIF   DELETING   THEN
  UPDATE   T_TOTALSURPLUS   SET   T_TOTALSURPLUS.SRPLS_HR=(T_TOTALSURPLUS.SRPLS_HR-:NEW.AHR)   WHERE   T_TOTALSURPLUS.IDS=:NEW.IDS   AND   T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
    END   IF;
          EXCEPTION
              WHEN   OTHERS   THEN
              &n