This discussion is archived
7 Replies Latest reply: Jul 11, 2011 10:09 AM by Alexandra Robin RSS

instead of trigger on view

Alexandra Robin Newbie
Currently Being Moderated
I created a view to use in a tabular form and only need to update one column from one table of that view. How do I write the instead of trigger to accomplish this? The field that I am trying to update is a select list-yes/no column in the tabular form. Upon submit with the wizard MRU and the instead of trigger I wrote the update doesn't take effect.

Without the trigger I get this error:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Here's the view:
CREATE OR REPLACE FORCE VIEW  "GET_USERNAME_VW" ("DOC_INFO_ID", "DOC_TITLE", "DOC_LINK", "ECRNO", "OWNER", "ISO_NUMBER", "STATUS_ID", "FILE_TYPE", "APPROVAL_REQ", "APPROVED", "JOB_DESC", "USER_NAME") AS 
  select     "DOC_INFO"."DOC_INFO_ID" as "DOC_INFO_ID",
     "DOC_INFO"."DOC_TITLE" as "DOC_TITLE",
     "DOC_INFO"."DOC_LINK" as "DOC_LINK",
     "DOC_INFO"."ECRNO" as "ECRNO",
     "DOC_INFO"."OWNER" as "OWNER",
     "DOC_INFO"."ISO_NUMBER" as "ISO_NUMBER",
     "DOC_INFO"."STATUS_ID" as "STATUS_ID",
     "DOC_INFO"."FILE_TYPE" as "FILE_TYPE",
     "DOC_INFO"."APPROVAL_REQ" as "APPROVAL_REQ",
     "DOC_INFO"."APPROVED" as "APPROVED",
     "SH_JOB_DESCRIPTION"."JOB_DESC" as "JOB_DESC",
     "SH_EMPLOYEES"."USER_NAME" as "USER_NAME" 
 from     "SH_EMPLOYEES" "SH_EMPLOYEES",
     "SH_JOB_DESCRIPTION" "SH_JOB_DESCRIPTION",
     "DOC_INFO" "DOC_INFO" 
 where   "DOC_INFO"."OWNER"="SH_JOB_DESCRIPTION"."JOB_DESC"
 and     "SH_JOB_DESCRIPTION"."JOB_DESC_ID"="SH_EMPLOYEES"."JOB_DESC_ID"
 and "DOC_INFO"."STATUS_ID" IN (1,2)
/
Here's the trigger I created
CREATE TRIGGER GET_APPROVAL
INSTEAD OF UPDATE ON GET_USERNAME_VW
REFERENCING NEW AS n                 
FOR EACH ROW
BEGIN
update doc_info set approval_req = :n.approval_req
END;
As always, TIA!
Alexandra

Edited by: userRRRYB on Jul 11, 2011 4:16 PM - ADDED VIEW

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points