Simple Example of a stored procedure trigger job

Drop table test1;
Create Table test1 (
FID varchar2 (44) primary key not null,
Fname varchar2 (80 ),
Fage integer
);
Drop table Test2;
Create Table Test2 (
FID varchar2 (44) primary key not null,
Fname varchar2 (80 ),
Fage integer,
Fsex varchar2 (20)
);
--- Insert trigger ---------------------------------------------------------------------------------------
Drop trigger trig_insert_test;
Create or replace trigger trig_insert_test
After insert on test1
Referencing new as new_value
For each row
Begin
Insert into Test2 (FID, fname, Fage) values (: new_value.fid,: new_value.fname,: new_value.fage );
End;
============================================
Create or replace trigger trigger_insert_test
After insert on test1
Referencing new as new_value
For each row
Begin
Insert into test (FID, fname, Fage) values (: new_value.fid,: new_value.fname, new_value.fage)
End;
============================================
Insert into test1 (FID, fname, Fage) values (1, 'mark', 19 );
 
--- Update the trigger Lifecycle -----------------------------------------------------------------------------------
Create or replace trigger trig_update_test
After update on test1
Referencing new as new_value
For each row
Begin
Update Test2 set fname =: new_value.fname,
Fage =: new_value.fage
Where FID =: new_value.fid;
End;

Update test1 set fname = 'molica ', Fage = '16' where FID = '1 ';

--- Delete trigger ------------------------------------------------------------------------------
Create or replace trigger trig_delete_test
Before delete on test1
Referencing old as old_value --- Note that the delete trigger uses old
For each row
Begin
Delete from Test2
Where FID =: old_value.fid;
End;
Delete from test1 where FID = '1 ';

Insert into test1 select FID, fname, Fage from Test2;

---------------------- Use the process to synchronize test1 and Test2 ----------------------------------------
Create or replace procedure test1_procedure_test2
Exc_param varchar2 (200 );
Begin
Merge into test1 T1
Using Test2 T2
On (t1.fid = t2.fid)
When matched then
Update set t1.fname = t2.fname, t1.fage = t2.fage
When not matched then
Insert
(T1.fid, t1.fname, t1.fage)
Values
(T2.fid, t2.fname, t2.fage );
Commit;
Exception
When others then
Exc_param: = sqlerrm;
Rollback;
End test1_procedure_test2;
Bytes --------------------------------------------------------------------------------------------
-------------------- Call the Stored Procedure ---------------------------
Begin
Test1_procedure_test2;
Commit;
End;
-----------------------------------------------------------------

---------------------------- Create a job -----------------------------
Declare jobevd number;
Begin
SYS. dbms_job.submit (jobevd, 'test1 _ procedure_test2; ', sysdate, ''sysdate + 1/1440 ''); -- execute once every 1 minute
Commit;
End;
Bytes -------------------------------------------------------------------------------------------------
--------------------------------- Delete a job -------------------------------
Begin
Dbms_job.remove (202); ---- 202 job no.
End;
Bytes ------------------------------------------------------------------------------------------
----------------------------------- View job ------------------------------------------------
Select * From user_jobs
Bytes -----------------------------------------------------------------------------------------------
------------------------------ Run job ---------------------------------------------
Begin
Dbms_job.execute (202 );
End;
Bytes ------------------------------------------------------------------------------------