Home » SQL & PL/SQL » SQL & PL/SQL » Problem In Triggers
Problem In Triggers [message #396185] Sat, 04 April 2009 15:40 Go to next message
MR[T]
Messages: 62
Registered: March 2009
Member
Hello

Here is a trigger ,without any error ..,,


 CREATE OR REPLACE TRIGGER CHK_DML_ON_EMP BEFORE INSERT OR UPDATE OR DELETE ON EMP
 BEGIN
   INSERT INTO LOG_HIST_ON_EMP
 VALUES
 (NULL,NULL,NULL,NULL,NULL,USER ,SYSDATE ,TO_CHAR(SYSDATE,'HH24:MI.SS'));
 END ;
/


My teacher told me to update EMP after making it

 UPDATE EMP
 SET SAL=SAL+1000;



I am unable to understand thay why he is updating emp ..?
Trigger is for LOG_HIST_ON_EMP table , and now when im viewing LOG_HIST_ON_EMP table again ,then output is like that



SQL> select * from log_hist_on_emp ;

     EMPNO JOB              SAL     DEPTNO O PERFORM_BY                     OPEN_DATE OPER_TIM
---------- --------- ---------- ---------- - ------------------------------ --------- --------
                                             SCOTT                          05-APR-09 01:25.03



Cam someone Please tell me thay why there is only one row updating in LOG_HIST_ON_EMP .
Re: Problem In Triggers [message #396186 is a reply to message #396185] Sat, 04 April 2009 16:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because it is a statement level trigger, not a row level trigger.

Which means, it is getting executed once, BEFORE the UPDATE (or insert or delete) ON EMP.
Re: Problem In Triggers [message #396187 is a reply to message #396185] Sat, 04 April 2009 16:05 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
So these statement will execute , if I update department table or any other table .?


2) And why its giving me that output in LOG_HIST_ON_EMP TABLE .Its not updating SAL IN LOG_HIST_ON_EMP TABLE .?

select * from log_hist_on_emp ;

     EMPNO JOB              SAL     DEPTNO O PERFORM_BY                     OPEN_DATE OPER_TIM
---------- --------- ---------- ---------- - ------------------------------ --------- --------
                                             SCOTT                          05-APR-09 01:25.03

[Updated on: Sat, 04 April 2009 16:07]

Report message to a moderator

Re: Problem In Triggers [message #396189 is a reply to message #396187] Sat, 04 April 2009 16:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The trigger is on the EMP table.

Just as it says in the statement:

... BEFORE INSERT OR UPDATE OR DELETE ON EMP ...


Then the stuff between the BEGIN end the END gets executed, which happens to be an:

... INSERT INTO LOG_HIST_ON_EMP ...




Re: Problem In Triggers [message #396191 is a reply to message #396185] Sat, 04 April 2009 16:24 Go to previous message
MR[T]
Messages: 62
Registered: March 2009
Member
Thanks alot ThomasG .You solved mY problem.


Now i think i have understand , We are making LOG_HIST_ON_EMP TABLE FOR SAVING RECORD ,that whenever any user update EMP table ,save date ,time and username .
Previous Topic: Need Help Posting date
Next Topic: How to work on a table type in a procedure
Goto Forum:
  


Current Time: Tue Dec 06 08:48:37 CST 2016

Total time taken to generate the page: 0.11808 seconds