7 Replies Latest reply: Jul 11, 2011 12:09 PM by Alexandra Robin RSS

    instead of trigger on view

    Alexandra Robin
      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