Triggers_Records_Cursors_Units.pdf - Create or replace trigger secure_emp Before Insert or update or delete On emp for each row declare Begin

Triggers_Records_Cursors_Units.pdf - Create or replace...

This preview shows page 1 - 7 out of 21 pages.

1 Create or replace trigger secure_emp Before Insert or update or delete On emp -- for each row -- declare Begin IF ( to _char (sysdate,’DY’) in (‘THU’,’FRI’) OR To_number (To_char (sysdate,’HH24’) not between 8 and 18)) THEN Raise_application_error (-20500, ‘ You May Only Insert Into EMP During Normal Hours’) ; End if; End; ****************************Execution ***************************** INSERT INTO emp (empno, ename, deptno) VALUES (7745, ‘ALI’, 40);
Image of page 1
2 Create or Replace Trigger secure_emp Before Insert or Update or Delete On emp Begin IF ( to _char (sysdate,’DY’) in (‘THU’,’FRI’) OR To_number (To_char (sysdate,’HH24’) not between 8 and 18)) THEN IF Inserting THEN Raise_application_error ( -20500 , 'You may only insert into EMP during normal hours'); ELSIF Deleting THEN Raise_application_error (-20502 ,’you may only delete from EMP during normal hours'); ELSIF Updating ('SAL') THEN Raise_application_error (-20503,'You may only update SAL during normal hours'); ELSE Raise_application_error (-20504,'You may only update EMP during normal hours'); END IF; END if; End;
Image of page 2
Create table audit_table (user_name varchar2(20), Tablename varchar2(20), Column_name varchar2(20), Ins number(5), Upd number(5), Del number(5), Max_ins number(5), Max_upd number(5), Max_del number(5) ); Insert into audit_table Values (‘SCOTT’, ‘EMP’, ‘SAL’, null , 1 , null , null , 5, null); 3
Image of page 3
4 Create or replace trigger check_salary_count After Update of sal On emp -- for each row Declare V_salary_changes NUMBER ; V_max_changes NUMBER ; Begin Update audit_table Set upd = upd + 1; Select upd , max_upd into v_salary_changes , v_max_changes From audit_table Where user_name = USER AND tablename = 'EMP' AND column_name = ‘SAL’ ; IF v_salary_changes > v_max_changes THEN Raise_application_error (-20501 , ' You may only make a maximum of ' || TO_CHAR ( v_max_changes ) || ' changes to the SAL column ') ; END IF; END; Update emp Set sal= sal + 500;
Image of page 4
5 ****** Instead of **** >>>> VIEW ***** Create or replace view v1 As Select empno , ename , sal From emp Where sal > 4000; ----------------------------------------------------------------------- Create or replace view v2 As Select empno, ename, sal From emp Where sal > 1000000; -------------------------------------------------------------------------------------------- Create or replace trigger diverting Instead of Insert or update On v1 For each row -- declare Begin Insert into v2 Values (1521 , 'mohamed' , 2000 ); End; / ********************************************************************** Insert into v1 Values (3566 , 'ahmed ' , 15000);
Image of page 5