Thanks for the help but still need abit of advice on Oracle triggers

From: Damon Nicholas <so_excited_at_excite.com>
Date: Mon, 4 Jan 1999 11:36:32 +0800
Message-ID: <76pcpu$48j$1@mawar.singnet.com.sg>

Thank you very much Thomas....that kind's of restore my confidence in Oracle (and boost mine too).

I would really appreciate if you could give me some advice on my trigger code. (no obligations but thanks alot)

This is my trigger code....my department is running PL/SQL Release 2.3.4.0.0 and Oracle7 Server Release 7.3.4.0.0 at the back-end.

CREATE TRIGGER New_Staff_Benefit
/* This procedure fires upon insertion of new records into table A in Personal System. It extracts additional information from table B and store into variables. The variables and values from table A are then used to insert a new row in table C. */
AFTER INSERT ON Table_A
FOR EACH ROW
DECLARE

 v_Name VARCHAR2(35);
 v_Sex VARCHAR2(1);
 v_Ward VARCHAR2(2);

BEGIN

Extract the name and sex from Table_B that matches ID of officer. SELECT NAME, SEX INTO v_Name, v_Sex FROM Table_B WHERE ID = :new.ID;
Extract the eligible ward that officer is entitled to. SELECT WARD INTO v_Ward FROM WARD WHERE Salary_Group = :new.Salary_Group;
Insert as new row into Table_C. From Dual is used just to return variables
and make use of DECODE function. NULL values are used as certain fields in
Table C are empty. INSERT INTO Table_C SELECT :new.ID, DECODE(:new.Salary_Group, '1', 'A', '2', 'B', '3', 'C', '4', 'D', '5', 'E'), NULL, NULL, DECODE(:new.Gradings, 1, 'A', 2, 'B', 3, 'C'), v_Name, :new.Dept, v_Sex, NULL, NULL, DECODE(:new.Leave_Type, '1', 'A', '2', 'B', '3', 'C'), v_Ward, NULL, TO_DATE(:new.DTE_JOIN_DEPT, 'YYYYMMDD'), NULL, 'P' FROM DUAL;

END;

---------------------------snip-----------------------

A copy of this was sent to "Damon Nicholas" <so_excited_at_excite.com> (if that email address didn't require changing) On Mon, 4 Jan 1999 09:47:24 +0800, you wrote:

>Hi, i'm very new in Oracle triggers (in fact in anything that is Oracle)
and
>is encountering some problems with them.
>
>I tried to create an Oracle trigger on a table but it did not work
properly.
>According to my DBA, the trigger did not fired because the end-users access
>the table through views and i am pretty confused. The view on the table is
a
>simple one i.e. one view for one table.
>

Triggers are defined on tables. Views are defined on tables. A table may be
inserted into via a view and that will fire all of the triggers and constraints
defined on the base table. The end user only needs INSERT access on the view
and that will fire any INSERT trigger defined on the base table in the view.

>My question is can i by giving rights to both the table and view to the
>user, get the trigger to fire off when an insert SQL statement is executed
>that writes a new record to a view?
>
Received on Sun Jan 03 1999 - 21:36:32 CST