Sometimes you need to insert (or update) a set of a tables with a single SQL instruction. The reasons can be many and many, let’s only say that instead of using a stored procedure or whatever, you can easily and effectly use the INSTEAD OF trigger approach.
Using INSTEAD OF triggers, you can enforce new business rules by extending or replacing the normal triggering actions of an INSERT, UPDATE, or DELETE statement.
Here a simple “real-life” example:
Having a view so defined:
CREATE OR REPLACE VIEW PM_TASKS_INSERT_VIEW AS
SELECT EnvName, ClientName, TaskName, TimeStamp, Elapsed
FROM PM_Environment e, PM_Client c, PM_Task t, PM_Taskrepository tr
WHERE e.EnvID = tr.EnvID
AND c.ClientID = tr.ClientID
AND t.TaskID = tr.ClientID
We can handle the virtual insert statement doing so:
create or replace trigger PM_TASKS_INSERT_VIEW_INS_TRG
instead of insert on pm_tasks_insert_view
for each row
declare
EnvRow PM_ENVIRONMENT%rowtype := NULL;
ClientRow PM_Client%rowtype := NULL;
TaskRow PM_Task%rowtype := NULL;
emesg VARCHAR2(250);
begin
--
-- Retrieve all the key from the Names...
--
BEGIN
select *
into EnvRow
from PM_ENVIRONMENT
where upper(EnvName) = upper(:NEW.EnvName);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (-20001,
'No environment found for the name: ' || :NEW.EnvName);
WHEN TOO_MANY_ROWS
THEN
raise_application_error (-20002,
'Too many environments rows found for the name: ' || :NEW.EnvName);
WHEN OTHERS THEN
emesg := SQLERRM;
raise_application_error (-20003,
'Generic error found trying to retrieve the environment: ' || emesg);
END;
BEGIN
select *
into ClientRow
from PM_Client
where upper(ClientName) = upper(:NEW.ClientName);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (-20011,
'No client found for the name: ' || :NEW.ClientName);
WHEN TOO_MANY_ROWS
THEN
raise_application_error (-20012,
'Too many client rows found for the name: ' || :NEW.ClientName);
WHEN OTHERS THEN
emesg := SQLERRM;
raise_application_error (-20013,
'Generic error found trying to retrieve the client: ' || emesg);
END;
BEGIN
select *
into TaskRow
from PM_Task
where upper(TaskName) = upper(:NEW.TaskName);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (-20021,
'No task found for the name: ' || :NEW.TaskName);
WHEN TOO_MANY_ROWS
THEN
raise_application_error (-20022,
'Too many task rows found for the name: ' || :NEW.TaskName);
WHEN OTHERS THEN
emesg := SQLERRM;
raise_application_error (-20023,
'Generic error found trying to retrieve the task: ' || emesg);
END;
--
-- Insert the new row on the "FACTS": table
--
BEGIN
insert into PM_TASKREPOSITORY (EnvID, ClientID, TaskID, TimeStamp, Elapsed)
values (EnvRow.Envid, ClientRow.Clientid, TaskRow.Taskid, :NEW.TimeStamp, :NEW.Elapsed);
EXCEPTION
WHEN WHEN DUP_VAL_ON_INDEX THEN
THEN
raise_application_error (-20031,
'Fact already inserted: ' || :NEW.TaskName);
END;
end PM_TASKS_INSERT_VIEW_INS_TRG;
As you can see, we’re inserting on the table PM_TASKREPOSITORY INSTEAD OF on the view PM_TASKS_INSERT_VIEW and - before of that - we look for additional values and so on.
The said piece of SQL is executed each time we issue an INSERT INTO PM_TASKS_INSERT_VIEW VALUES(…).
As already said, easy and very effective!
For more details click here.
