用触发器可以实现create trigger name_tri before delete on a for each row begin delete from table2 where column=:new.column; end; /
不能这样写,我理解你是想删除table1中数据,应这样写: delete from table1 A where exists ( select 'x' from table2 B where A.column ='0000' and B.column = A.column ) 如你想删除table2的,换一下名字就可以了。
只能一个个表的删除。 但你可以做sql的批处理,但还不如一条条写简单--因为你只是做一点点事情。
delete table a from (select * from a,b where a.col = b.col) where a.col = '000'
DECLARE CURSOR c1 IS SELECT * FROM table1 WHERE COLUMN='0000' FOR UPDATE; l_c1 c1%ROWTYPE; i INTEGER; BEGIN OPEN c1; LOOP FETCH c1 INTO l_c1; EXIT WHEN c1%NOTFOUND; SELECT COUNT(*) INTO i FROM table2 WHERE COLUMN=l_c1.COLUMN; IF i>0 THEN DELETE FROM table2 WHERE COLUMN=l_c1.COLUMN; DELETE FROM table1 WHERE CURRENT OF c1; END IF; END LOOP COMMIT; CLOSE c1; END; 这样一段pl/sql可以完成你的功能。
before delete on a
for each row
begin
delete from table2 where column=:new.column;
end;
/
delete from table1 A
where exists
(
select 'x' from table2 B
where A.column ='0000'
and B.column = A.column
)
如你想删除table2的,换一下名字就可以了。
但你可以做sql的批处理,但还不如一条条写简单--因为你只是做一点点事情。
不过用plsql写段脚本,可以完成两个表的同时删除,一条语句一定办不到
CURSOR c1 IS
SELECT * FROM table1 WHERE COLUMN='0000' FOR UPDATE;
l_c1 c1%ROWTYPE;
i INTEGER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO l_c1;
EXIT WHEN c1%NOTFOUND;
SELECT COUNT(*) INTO i FROM table2 WHERE COLUMN=l_c1.COLUMN;
IF i>0 THEN
DELETE FROM table2 WHERE COLUMN=l_c1.COLUMN;
DELETE FROM table1 WHERE CURRENT OF c1;
END IF;
END LOOP
COMMIT;
CLOSE c1;
END;
这样一段pl/sql可以完成你的功能。