My requirement is to come up with a Trigger which will update a Column of a table once the event takes place.
Structure of the table is given below. Table name Broadcast
Column Name | Data Type |
BRDCST_MSGID | Number(10) (PK) |
BRDCST_MSG_SBJ_TXT | Varchar2 (25) |
BRDCST_MSG_TXT | Varchar2 (2000) |
BRDCST_MSG_EFCTV_DT | Date |
BRDCST_MSG_TERMNT_DT | Date |
LAST_UPDT_TIMSTM | Date |
STATUS | Varchar2(10) |
Broadcast |
BrdCast_MsgID | BRDCST_MSG_SBJ_TXT | BRDCST_MSG | BRDCST_MSG_EFCTV_DT | BRDCST_MSG_TERMNT_DT | LAST_UPDT_TIMSTM | STATUS |
1 | Unavailability of the application | Application will not be available from 19-May,2014 till 22-May-2014,due to maintenance activity. | 5/19/2014 | 5/22/2014 | 5/19/2014 | Active |
If the Sysdate is equal to BRDCST_MSG_EFCTV_DT the value of the STATUS column will get updated to Active , Last_updt_timstm=Sysdate ,Again STATUS column become INACTIVE when the Sysdate is equal to BRDCST_MSG_TERMNT_DT and Last_updt_timstm=Sysdate.
Create or Replace Trigger Update_Broadcast
After Insert on Broadcast for each row
Begin
If BRDCST_MSG_EFCTV_DT=Sysdate;
then
Update Broadcast
Set Last_updt_timstm=Sysdate,STATUS=’Active’
Where BRDCST_MSG_EFCTV_DT =Sysdate;
Elsif
BRDCST_MSG_TERMN_DT=Sysdate
Then
Update Broadcast
Set Last_updt_timstm=Sysdate, STATUS=INACTIVE
Where BRDCST_MSG_TERMNT_DT =Sysdate;
Endif;
End B Update_Broadcast;
I am getting an error while executing the above mentioned trigger.Need your help to meet this business requirement.
Thanks in advance.