StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If the trigger condition is “always” verified in the table, and if DE_TRANSFORM_MAP is a small table or if the insert/update statement affects many rows in DE_TRANSFORM_MAP, then you can use a statement trigger like this:</p> <pre><code>CREATE OR REPLACE TRIGGER DETRANSFORMMAP_VALID_TRIG AFTER INSERT OR UPDATE ON DE_TRANSFORM_MAP DECLARE EXISTS_ROWS NUMBER; BEGIN SELECT 1 INTO EXISTS_ROWS FROM DUAL WHERE EXISTS( SELECT CLIENT FROM DE_TRANSFORM_MAP WHERE IS_ACTIVE = 1 GROUP BY CLIENT, USE_CASE HAVING COUNT(*) > 1 ); IF (EXISTS_ROW = 1) THEN RAISE_APPLICATION_ERROR(-20000, 'Only one row with the specified client, use_case, policy_id and policy_level may be active'); END IF; END; / </code></pre> <p>If the trigger condition is “not always” verified in the table, and if DE_TRANSFORM_MAP is a big table or if the insert/update statement affects few rows in DE_TRANSFORM_MAP, then redesign your trigger following Rene's answer. Something like:</p> <pre><code>CREATE GLOBAL TEMPORARY TABLE DE_TRANSFORM_MAP_AUX AS SELECT CLIENT, USE_CASE FROM DE_TRANSFORM_MAP WHERE 1 = 0; / CREATE OR REPLACE TRIGGER DETRANSFORMMAP_VALID_TRIG1 BEFORE INSERT OR UPDATE ON SERAPH.DE_TRANSFORM_MAP BEGIN DELETE FROM DE_TRANSFORM_MAP_AUX; END; / CREATE OR REPLACE TRIGGER DETRANSFORMMAP_VALID_TRIG2 BEFORE INSERT OR UPDATE ON DE_TRANSFORM_MAP FOR EACH ROW WHEN NEW.IS_ACTIVE = 1 BEGIN INSERT INTO DE_TRANSFORM_MAP_AUX VALUES(:NEW.CLIENT, :NEW.USE_CASE); END; / CREATE OR REPLACE TRIGGER DETRANSFORMMAP_VALID_TRIG3 AFTER INSERT OR UPDATE ON DE_TRANSFORM_MAP DECLARE EXISTS_ROW NUMBER; BEGIN SELECT 1 INTO EXISTS_ROWS FROM DUAL WHERE EXISTS( SELECT CLIENT FROM DE_TRANSFORM_MAP WHERE IS_ACTIVE = 1 AND (CLIENT, USE_CASE) IN (SELECT CLIENT, USE_CASE FROM DE_TRANSFORM_MAP_AUX) GROUP BY CLIENT, USE_CASE HAVING COUNT(*) > 1 ); DELETE FROM DE_TRANSFORM_MAP_AUX; IF (EXISTS_ROW = 1) THEN RAISE_APPLICATION_ERROR(-20000, 'Only one row with the specified client, use_case, policy_id and policy_level may be active'); END IF; END; / </code></pre> <p>You must consider to create an index on CLIENT and USE_CASE in DE_TRANSFORM_MAP if it does not exists.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload