我将table中的某一笔资料删除后想要在triggers中将最靠近(删除的那笔资料),的某个栏位值变成null为什么triggers中却无法使用partition by呢??程式码如下
IF DELETING THEN
UPDATE aaa D
SET D.a =''
WHERE D.b = (
SELECT b FROM (select T.*,row_number() over (partition by dep order by date_1,time_1 DESC) P from aaa t )→错误会在这
WHERE dep = :OLD.dep
WHERE a = 'n'
AND P = 1)
AND D.date_1 = (
SELECT date_1 FROM (select T.*, row_number() over (partition by dep order by date_1,time_1 DESC) NUM from aaa t)→错误会在这
WHERE dep = :OLD.dep
AND a = 'n'
AND NUM = 1);
END IF;table资料如下
dep date_1 time_1 b
a 20110512 12:30 y
a 20110512 14:40 y
a 20100530 13:00 →我把这笔删除后,系统会把20110512,4:40这笔的b栏位改成null请问在triggers中我该如何修改这段程式码,或是有更精简的方法可以让我完成我需要的资料,拜托了谢谢..
IF DELETING THEN
UPDATE aaa D
SET D.a =''
WHERE D.b = (
SELECT b FROM (select T.*,row_number() over (partition by dep order by date_1,time_1 DESC) P from aaa t )→错误会在这
WHERE dep = :OLD.dep
WHERE a = 'n'
AND P = 1)
AND D.date_1 = (
SELECT date_1 FROM (select T.*, row_number() over (partition by dep order by date_1,time_1 DESC) NUM from aaa t)→错误会在这
WHERE dep = :OLD.dep
AND a = 'n'
AND NUM = 1);
END IF;table资料如下
dep date_1 time_1 b
a 20110512 12:30 y
a 20110512 14:40 y
a 20100530 13:00 →我把这笔删除后,系统会把20110512,4:40这笔的b栏位改成null请问在triggers中我该如何修改这段程式码,或是有更精简的方法可以让我完成我需要的资料,拜托了谢谢..
create or replace trigger tr_tab1
after delete on tab1
begin
update tab1 set ............
end;虽然可以使用自治事务,但容易死锁,最好不要这样做。
create or replace trigger tr_tab1
after delete on tab1
pragma autonomous_transaction;
begin
update tab1 set ............
end;最好在外部实现。
试过您所说的create or replace trigger tr_tab1
after delete on tab1
他出现的错误讯息是"触发程式/函数无法检试它"请问
当我把B栏位为空值的资料删掉的时候要去把最靠近被删除那笔资料的B栏位打开我要怎么写呢???
然后在把打开的那笔资料的DEP值输入到BBB资料表中的PP栏位????
怎么在处里的时候就把逻辑处理掉?
可不可以举个例子