| Delete trigger [message #7361] |
Sat, 07 June 2003 03:40  |
Victor Behar
Messages: 1
Registered: June 2003
|
Junior Member |
|
|
Oracle8i Enterprise Edition Release 8.1.7.0.0
An error 4091 is returned by the following trigger. The same error is produced if the trigger is changed to before delete with KOUNT tested for 1.
The requirement is to delete the appropriate rows from MARKET_PRODUCT when the last row on WAREHOUSE_PRODUCT for that product and owner have been deleted.
This would appear to be a common requirement. Can it be done with a trigger?
Comments gratefully received.
Victor
CREATE OR REPLACE TRIGGER WP_TRIGGER_2
after delete on WAREHOUSE_PRODUCT
for each row
DECLARE
KOUNT NUMBER(4) DEFAULT 0;
BEGIN
SELECT count(1) into KOUNT
FROM WAREHOUSE_PRODUCT
WHERE WP_PRODUCT_CODE = :old.WP_PRODUCT_CODE
AND WP_OWNER_CODE = :old.WP_OWNER_CODE;
If KOUNT = 0
Then
delete from MARKET_PRODUCT
where MP_PRODUCT_CODE = :old.WP_PRODUCT_CODE
and MP_MARKET_CODE = 'STER'
and MP_OWNER_CODE = :old.WP_OWNER_CODE;
end if;
END;
|
|
|
|
|
|
| Re: maybe use VIEW Trigger instead of TABLE trigger [message #7367 is a reply to message #7361] |
Mon, 09 June 2003 00:10  |
nyfor
Messages: 21
Registered: March 2003
|
Junior Member |
|
|
if a primary key exists on table WAREHOUSE_PRODUCT,you can:
(assume primary key (id))
create view VIEW_WAREHOUSE_PRODUCT
as select * from WAREHOUSE_PRODUCT;
CREATE OR REPLACE TRIGGER WP_TRIGGER_2
instead of delete on VIEW_WAREHOUSE_PRODUCT
for each row
DECLARE
KOUNT NUMBER(4) DEFAULT 0;
BEGIN
delete WAREHOUSE_PRODUCT where id = :old.id;
SELECT count(1) into KOUNT
FROM WAREHOUSE_PRODUCT
WHERE WP_PRODUCT_CODE = :old.WP_PRODUCT_CODE
AND WP_OWNER_CODE = :old.WP_OWNER_CODE;
If KOUNT = 0
Then
delete from MARKET_PRODUCT
where MP_PRODUCT_CODE = :old.WP_PRODUCT_CODE
and MP_MARKET_CODE = 'STER'
and MP_OWNER_CODE = :old.WP_OWNER_CODE;
end if;
END;
delete VIEW_WAREHOUSE_PRODUCT where ...
-- instead of following
-- delete WAREHOUSE_PRODUCT where ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
