Write before:

Recently, the old project has added the need of daily report optimization. Li Jie asked me to use triggers to update the daily report data when inserting or updating data. Mm-hmm-hmm. when I was learning database, I met triggers, but they all jumped by, and I didn't really practice them. This time, I have the right to learn again~~

1. Trigger instance:

--Create trigger
create or replace trigger test_trigger2
--Trigger is in insert or update Performed after action
after insert or update
--Act on dms.kqm_leaveapply Each row of the table
on dms.kqm_leaveapply
--The default here is new ,old If there is any conflict with other variables, it has been modified
referencing new as new_val old as old_val
for each row
--Trigger constraints  
when (new_val.statusname = 'approval')
--In trigger description statement, new data is used new_val In the following operation statement, the new data uses the:new_val Citing

declare  --Declare variables
   st_str varchar2(100);
   et_str varchar2(100);

   time1 number;
   time2 number;
   ......
begin
   ......

   --Query result set, traverse

   for daylist in (
     --Find all dates in the specified time period
     select to_char(:new_val.startdate+rownum-1,'yyyymmdd') as dd
     from dual
     connect by rownum <=
          trunc(:new_val.enddate - :new_val.startdate)+1
   )
   loop
     
        --Write the logic to operate
        ......
         --Update data from another table 
         update dms.d_dailyreport_t d set d.shours =  (8 - ItemValue)
         where d.personcode = :new_val.workno and  to_char(d.ftime,'yyyy/mm/dd') like to_char(daylist.dd,'yyyy/mm/dd')  ;

   end loop;
  
     --substr()Data interception
     st_str :=substr(:new_val.starttime,1,2) || substr(:new_val.starttime,4,2) ;
     et_str := substr(:new_val.endtime,1,2) || substr(:new_val.endtime,4,2) ;
     time1 := to_number(st_str);
     time2 := to_number(et_str);

     if(Item_m<0) then
         Item_m := Item_m *(-1);
         tHour := tHour -1;
     end if;

end test_trigger2;

The trigger example above is just a framework and cannot be executed. Here we only make simple records. There are many examples on the Internet. After a little look, we will be able to get a simple introduction. Here we will not make more records and explanations~~