CREATE OR REPLACE TRIGGER AUTODEL_XT
AFTER INSERT ON XTDATA
BEGIN
IF(SELECT COUNT(*) FROM XTDATA) = 600
DELETE FROM XTDATA WHERE XTTIME=(SELECT MIN(XTTIME) FROM XTDATA)
END
AFTER INSERT ON XTDATA
BEGIN
IF(SELECT COUNT(*) FROM XTDATA) = 600
DELETE FROM XTDATA WHERE XTTIME=(SELECT MIN(XTTIME) FROM XTDATA)
END
我翻來覆去看了幾遍都沒錯.to nanman(南蛮):
你這是SQL Server的寫法,可他是Oracle
AFTER INSERT ON XTDATA
declare v_number number;
BEGIN
select count(*) into v_number from xtdata;
IF v_number=600 then
DELETE FROM XTDATA WHERE XTTIME=(SELECT MIN(XTTIME) FROM XTDATA);
END if;
end;
/
AFTER INSERT OR UPDATE OR DELETE ON tables
FOR EACH ROW
DECLARE
lRowNum INTEGER := 0 ;
lAction CHAR(1);
BEGIN IF INSERTING THEN
LACTION := '0';
SELECT COUNT(*) INTO lrownum FROM tables_EXCH
WHERE DIRECTION = '0' AND CIQ_REG_NO = :NEW.CIQ_REG_NO
AND CERT_KIND = :NEW.CERT_KIND AND CERT_NO = :NEW.CERT_NO; ELSIF DELETING THEN
LACTION := '1';
SELECT COUNT(*) INTO lrownum FROM tables_EXCH
WHERE DIRECTION = '0' AND CIQ_REG_NO = :old.CIQ_REG_NO
AND CERT_KIND = :old.CERT_KIND AND CERT_NO = :old.CERT_NO; END IF;
END;
joe225(乔乔)的方法竟然可以了!!
但是为什么一定要赋值那?
有什么不同?回答了马上结帐
to: mashansj(风影)
TRIGGER XTDATA 出现错误:LINE/COL ERROR
-------- ---------------------------------------------------------
2/5 PLS-00103: 出现符号 "SELECT"在需要下列之一时:
(-+modnotnullothers
<an identifier><a double-quoted delimited-identifier>
<a bind variable>avgcountcurrentexistsmaxminpriorsqlstdde
sumvarianceexecuteforalltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>2/35 PLS-00103: 出现符号 "="在需要下列之一时:
;returnreturningandor
IF(SELECT COUNT(*) FROM XTDATA) = 600 THEN
...
END IF;
在oracle中有这种写法吗?我第一次见到!反正我用时是编释时是没有通过!
我建议你用 joe225的试试!
你的版本?
begin
end
中
end後加上;
必须把SELECT COUNT(*) 送去一个变量里面, 就象joe225(乔乔)写的. 因为是在INSERT 以后,所以意外控制不需要.
SQL是面向集合的操作,事实上IF后的SELECT语句并没有被提交,也就是根本无法判断。所以,必须先赋值,再判断。