Oracle 10g and data validation in a trigger before row update

Question

I am using Oracle 10g and I have the following table:

create table DE_TRANSFORM_MAP
(
  DE_TRANSFORM_MAP_ID NUMBER(10) not null,
  CLIENT              NUMBER(5) not null,
  USE_CASE            NUMBER(38) not null,
  DE_TRANSFORM_NAME   VARCHAR2(100) not null,
  IS_ACTIVE           NUMBER(1) not null
)

That maps to an entry in the following table:

create table DE_TRANSFORM
(
  DE_TRANSFORM_ID NUMBER(10) not null,
  NAME            VARCHAR2(100) not null,
  IS_ACTIVE       NUMBER(1) not null
)

I would like to enforce the following rules:

• Only one row in DE_TRANSFORM_MAP with the same CLIENT and USE_CASE can have IS_ACTIVE set to 1 at any time
• Only one row in DE_TRANSFORM with the same NAME and IS_ACTIVE set to 1 at any time
• A row in DE_TRANSFORM cannot have IS_ACTIVE changed from 1 to 0 if any rows in DE_TRANSFORM_MAP have DE_TRANSFORM_NAME equal to NAME and IS_ACTIVE set to 1

Does this make sense?

I have tried to write a stored proc that handles this:

create or replace trigger DETRANSFORMMAP_VALID_TRIG
after insert or update on SERAPH.DE_TRANSFORM_MAP
for each row
declare
   active_rows_count NUMBER;
begin
   select count(*) into active_rows_count from de_transform_map where client = :new.client and use_case = :new.use_case and is_active = 1;
   if :new.is_active = 1 and active_rows_count > 0 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;

When I do the following it works as expected, I get an error:

insert into de_transform_map (de_transform_map_id, client, use_case, de_transform_name, is_active) values (detransformmap_id_seq.nextval, 6, 0, 'TEST', 1);
insert into de_transform_map (de_transform_map_id, client, use_case, de_transform_name, is_active) values (detransformmap_id_seq.nextval, 6, 1, 'TEST', 1);

But if I then do this:

update de_transform_map set use_case = 0 where use_case = 1

I get the following:

ORA-04091: table DE_TRANSFORM_MAP is mutating, trigger/function may not see it

How can I accomplish my validation?

EDIT: I marked Rene's answer as correct because I think the most correct and elegant way to do this is with a compound trigger but our production DB is still just 10g, we are updating to 11g early next year and I will rewrite the trigger then. Until then, I have a blanket trigger that will assert that no rows are duplicated, here it is:

create or replace trigger DETRANSFORMMAP_VALID_TRIG
after insert or update on DE_TRANSFORM_MAP
declare
   duplicate_rows_exist NUMBER;
begin
   select 1 into duplicate_rows_exist from dual where exists (
     select client, use_case, count(*) from de_transform_map where is_active = 1
      group by client, use_case
       having count(*) > 1
   );
   if duplicate_rows_exist = 1 then
      RAISE_APPLICATION_ERROR(-20000, 'Only one row with the specified client, use_case may be active');
   end if;
end;

Answer 1

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:

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;
/

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:

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;
/

You must consider to create an index on CLIENT and USE_CASE in DE_TRANSFORM_MAP if it does not exists.

Answer 2

The error you get means that you cannot query the table the trigger is on from within a row level trigger itself. One way to work around this problem is to use a combination of 3 triggers.

• a) A before statement level trigger
• b) A row level trigger
• c) An after statement level trigger

Trigger A initializes a collection in a package

Trigger B adds every changed row to the collection

Trigger C performs the desired action for every entry in the collection.

More details here: http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

One of the improvements in Oracle 11G is that you can do all these action in one compound trigger. More here: http://www.oracle-base.com/articles/11g/trigger-enhancements-11gr1.php

Answer 3

You should perhaps consider doing a "before insert" sort of thing! I've only got an MSSQL engine to play with right now, but hopefully something below might help you on your way... I'm not sure what you mean with your example of an error that works, however, as it appears to be in contradiction to the first use case you've posted... Either way, triggers can be a real pain during concurrent writes so you'll want to be careful in doing this sort of business logic validation from only the back end.

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'DE_TRANSFORM_MAP'
                    AND type = 'U' )
BEGIN
    --DROP TABLE DE_TRANSFORM_MAP;
    CREATE TABLE DE_TRANSFORM_MAP
    (
        DE_TRANSFORM_MAP_ID NUMERIC(10) NOT NULL, 
                                PRIMARY KEY ( DE_TRANSFORM_MAP_ID ),
        CLIENT              NUMERIC( 5 ) NOT NULL,
        USE_CASE            NUMERIC( 38 ) NOT NULL,
        DE_TRANSFORM_NAME   NVARCHAR( 100 ) NOT NULL,
        IS_ACTIVE           TINYINT NOT NULL
    );
END;

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'DE_TRANSFORM'
                    AND type = 'U' )
BEGIN
    --DROP TABLE DE_TRANSFORM;
    CREATE TABLE DE_TRANSFORM
    (
        DE_TRANSFORM_ID     NUMERIC( 10 ) NOT NULL, 
                            PRIMARY KEY ( DE_TRANSFORM_ID ),
        NAME                NVARCHAR( 100 ) NOT NULL,
        IS_ACTIVE           TINYINT NOT NULL                                        
    );
END;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'DETRANSFORMMAP_VALID_TRIG'
                    AND type = 'TR' )
BEGIN
    --DROP TRIGGER DETRANSFORMMAP_VALID_TRIG;
    EXEC( '
        CREATE TRIGGER DETRANSFORMMAP_VALID_TRIG 
            ON DE_TRANSFORM_MAP INSTEAD OF INSERT, UPDATE
        AS SET NOCOUNT OFF;' );
END;
GO

ALTER TRIGGER DETRANSFORMMAP_VALID_TRIG 
    ON DE_TRANSFORM_MAP INSTEAD OF INSERT, UPDATE
AS BEGIN
    SET NOCOUNT ON;

    IF ( (  SELECT  MAX( IS_ACTIVE )
            FROM (  SELECT  IS_ACTIVE = SUM( IS_ACTIVE )
                    FROM (  SELECT  CLIENT, USE_CASE, IS_ACTIVE
                            FROM    DE_TRANSFORM_MAP
                            EXCEPT
                            SELECT  CLIENT, USE_CASE, IS_ACTIVE
                            FROM    DELETED
                            UNION ALL
                            SELECT  CLIENT, USE_CASE, IS_ACTIVE
                            FROM    INSERTED ) f
                    GROUP BY CLIENT, USE_CASE ) mf ) > 1 )
    BEGIN
        RAISERROR( 'DE_TRANSFORM_MAP: CLIENT & USE_CASE cannot have multiple actives', 16, 1 );
    END ELSE BEGIN
        DELETE  DE_TRANSFORM_MAP
        WHERE   DE_TRANSFORM_MAP_ID IN (    SELECT  DE_TRANSFORM_MAP_ID
                                            FROM    DELETED );

        INSERT INTO DE_TRANSFORM_MAP ( DE_TRANSFORM_MAP_ID, 
            CLIENT, USE_CASE, DE_TRANSFORM_NAME, IS_ACTIVE )
        SELECT  DE_TRANSFORM_MAP_ID, CLIENT, USE_CASE, 
                DE_TRANSFORM_NAME, IS_ACTIVE
        FROM    INSERTED;
    END;

    SET NOCOUNT OFF;
END;
GO

INSERT INTO DE_TRANSFORM_MAP ( DE_TRANSFORM_MAP_ID, 
    CLIENT, USE_CASE, DE_TRANSFORM_NAME, IS_ACTIVE )
VALUES ( 1, 6, 0, 'TEST', 1 );

INSERT INTO DE_TRANSFORM_MAP ( DE_TRANSFORM_MAP_ID, 
    CLIENT, USE_CASE, DE_TRANSFORM_NAME, IS_ACTIVE )
VALUES ( 2, 6, 1, 'TEST', 1 );
GO

SELECT  *
FROM    dbo.DE_TRANSFORM_MAP;
GO

TRUNCATE TABLE DE_TRANSFORM_MAP;
GO

INSERT INTO DE_TRANSFORM_MAP ( DE_TRANSFORM_MAP_ID, 
    CLIENT, USE_CASE, DE_TRANSFORM_NAME, IS_ACTIVE )
            SELECT  1, 6, 0, 'TEST', 1
UNION ALL   SELECT  2, 6, 1, 'TEST', 1
UNION ALL   SELECT  3, 6, 1, 'TEST2', 1;
GO

SELECT  *
FROM    dbo.DE_TRANSFORM_MAP;
GO

TRUNCATE TABLE DE_TRANSFORM_MAP;
GO

INSERT INTO DE_TRANSFORM_MAP ( DE_TRANSFORM_MAP_ID, 
    CLIENT, USE_CASE, DE_TRANSFORM_NAME, IS_ACTIVE )
            SELECT  1, 6, 0, 'TEST', 1
UNION ALL   SELECT  2, 6, 1, 'TEST', 0
UNION ALL   SELECT  3, 6, 1, 'TEST2', 1;
GO

SELECT  *
FROM    dbo.DE_TRANSFORM_MAP;
GO

UPDATE  dbo.DE_TRANSFORM_MAP
    SET IS_ACTIVE = 1
WHERE   DE_TRANSFORM_MAP_ID = 2;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'DETRANSFORM_VALID_TRIG'
                    AND type = 'TR' )
BEGIN
    --DROP TRIGGER DETRANSFORM_VALID_TRIG;
    EXEC( '
        CREATE TRIGGER DETRANSFORM_VALID_TRIG 
            ON DE_TRANSFORM INSTEAD OF INSERT, UPDATE
        AS SET NOCOUNT OFF;' );
END;
GO

ALTER TRIGGER DETRANSFORM_VALID_TRIG 
    ON DE_TRANSFORM INSTEAD OF INSERT, UPDATE
AS BEGIN
    SET NOCOUNT ON;

    IF ( (  SELECT  MAX( IS_ACTIVE )
            FROM (  SELECT  IS_ACTIVE = SUM( IS_ACTIVE )
                    FROM (  SELECT  NAME, IS_ACTIVE
                            FROM    DE_TRANSFORM
                            EXCEPT
                            SELECT  NAME, IS_ACTIVE
                            FROM    DELETED
                            UNION ALL
                            SELECT  NAME, IS_ACTIVE
                            FROM    INSERTED ) f
                    GROUP BY NAME ) mf ) > 1 )
    BEGIN
        RAISERROR( 'DE_TRANSFORM: NAME cannot have multiple actives', 16, 1 );
    END ELSE IF EXISTS (SELECT  1
                        FROM    DE_TRANSFORM_MAP
                        WHERE   IS_ACTIVE = 1
                            AND DE_TRANSFORM_NAME IN (  SELECT  NAME
                                                        FROM    DELETED
                                                        UNION ALL
                                                        SELECT  NAME
                                                        FROM    INSERTED
                                                        WHERE   IS_ACTIVE = 0 ) )
    BEGIN
        RAISERROR( 'DE_TRANSFORM: NAME is active in DE_TRANSFORM_MAP', 16, 1 );
    END ELSE BEGIN

        DELETE  DE_TRANSFORM
        WHERE   DE_TRANSFORM_ID IN (SELECT  DE_TRANSFORM_ID
                                    FROM    DELETED );

        INSERT INTO DE_TRANSFORM ( DE_TRANSFORM_ID, NAME, IS_ACTIVE )
        SELECT  DE_TRANSFORM_ID, NAME, IS_ACTIVE
        FROM    INSERTED;
    END;

    SET NOCOUNT OFF;
END;
GO

INSERT INTO DE_TRANSFORM ( DE_TRANSFORM_ID, NAME, IS_ACTIVE )
VALUES( 1, 'TEST2', 0 );
GO

SELECT  *
FROM    DE_TRANSFORM;
GO

TRUNCATE TABLE DE_TRANSFORM;
GO

TRUNCATE TABLE DE_TRANSFORM_MAP;
GO