有个表 trans
字段 userid int; transdate datetime; price int;当插入trans时,如果select count(*) from trans where userid=插入的userid 超过20条记录,
则根据transdate排序,删除最早的那一条记录这个触发器如何写?
字段 userid int; transdate datetime; price int;当插入trans时,如果select count(*) from trans where userid=插入的userid 超过20条记录,
则根据transdate排序,删除最早的那一条记录这个触发器如何写?
BEFORE
INSERT
ON TEST
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
row_count INTEGER;
BEGIN
--
SELECT COUNT (*)
INTO row_count
FROM TEST; IF row_count > 20
THEN
DELETE TEST
WHERE transdate = (SELECT MIN (transdate)
FROM TEST);
END IF;
END trg_test_after_insert;这个可以实现,不过你要是想确保20条以内,可以在if 判断时加一个while 循环,删除到20条以下才执行insert操作。oracle 9.2下测试通过。