Thanks for the question, Yamini.
Asked: April 29, 2017 - 9:02 am UTC
Last updated: May 02, 2017 - 2:44 pm UTC
Version: 11 g
Viewed 1000+ times
You Asked
Hi Team,
I have to create one trigger. Whenever some insert happens I want that trigger to be fired.
Please see this query:
select dept_id, acct_airing_id, lag(dept_airing_id) over (partition by acct_airing_id order by dept_id) as old_dept_airing_id,
dept_airing_id, last_update_date,action_ind from dept where action_ind in('D','X','A') order by last_update_date desc ;
I want to add this query in trigger. This query output will give me after and before insert changes. I will be needing all those changes which happens in dept_airing_id.
I am trying to store this query output in a table, so that whenever any insert happens, trigger will be fired. And once the trigger will be fired, this query output will get stored in some table(I have created).
Can you please help me out , how can I insert this query output in a table using trigger. So that whenever that trigger fires, this select query runs and store its output in a table.
and Connor said...
Something like this should get you started
SQL> create table t
2 ( acct int,
3 dept_airing_id int ,
4 update_time date
5 )
6 /
Table created.
SQL>
SQL> create table t_audit
2 ( tstamp date,
3 acct int,
4 old_dept_airing_id int ,
5 new_dept_airing_id int
6 )
7 /
Table created.
SQL>
SQL> create or replace
2 trigger AUDIT_TRG for insert on T compound trigger
3
4 type each_row is record ( acct int,
5 dept_airing_id int,
6 update_time date
7 );
8
9 type row_list is table of each_row index by pls_integer;
10
11 g row_list;
12
13 before statement is
14 begin
15 g.delete;
16 end before statement;
17
18 after each row is
19 begin
20 g(g.count+1).acct := :new.acct;
21 g(g.count).dept_airing_id := :new.dept_airing_id;
22 g(g.count).update_time := :new.update_time;
23 end after each row;
24
25 after statement is
26 begin
27 for i in 1 .. g.count loop
28 insert into T_AUDIT
29 select sysdate, acct, old_air,dept_airing_id
30 from
31 (
32 select acct,
33 dept_airing_id,
34 lag(dept_airing_id) over ( order by update_time ) old_air,
35 row_number() over ( order by update_time desc ) as rn
36 from t
37 where acct = g(i).acct
38 )
39 where rn = 1;
40 end loop;
41 g.delete;
42 end after statement;
43
44 end;
45 /
Trigger created.
SQL>
SQL> sho err
No errors.
SQL> insert into t values (1,10,sysdate);
1 row created.
SQL> insert into t values (1,20,sysdate);
1 row created.
SQL> insert into t values (1,30,sysdate);
1 row created.
SQL> select * from t_audit;
TSTAMP ACCT OLD_DEPT_AIRING_ID NEW_DEPT_AIRING_ID
--------- ---------- ------------------ ------------------
01-MAY-17 1 10
01-MAY-17 1 10 20
01-MAY-17 1 20 30
3 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment