PL/SQL to Create or Replace Triggers, Varray and nested tables

Question: 1)
Create orreplace trigger trig_msg after update ordelete or insert on customer for each row
Code:
declare
oper varchar2(10);
begin
if updating then
DBMS_OUTPUT.PUT_LINE('UPDATING');
end if;
if deleting then
DBMS_OUTPUT.PUT_LINE('DELETING');
end if;
if inserting then
DBMS_OUTPUT.PUT_LINE('INSERTING');
end if;
end;
SQL> set serveroutput on;
SQL> insert into customer values(1,'vish','delhi',500,'21-jan-2001');
INSERTING
1 row created.
SQL> delete from customer where loanno = 1;
DELETING
1 row deleted.
Question 2)
create or replace trigger trig_nodml after update orinsert or delete on loan for each row
Code:
declare
m varchar2(15);
begin
if updating then
RAISE_APPLICATION_ERROR(-20000,'NO UPDATE ALLOWED');
end if;
if deleting then
RAISE_APPLICATION_ERROR(-20000,'NO DELETING ALLOWED');
end if;
if inserting then
m:=TO_CHAR(sysdate,'DAY');
if (upper(m)<> upper('sunday')) then
RAISE_APPLICATION_ERROR(-20000,'NOT ALLOWED ON WEEKDAY');
end if;
end if;
end;
SQL> insert into loan values(1,'vishal','delhi',2000);
insert into loan values(1,'vishal','delhi',2000)
*
ERROR at line 1:
ORA-20000: NOT ALLOWED ON WEEKDAY
ORA-06512: at "SCOTT.TRIG_NODML", line 13
ORA-04088: error during execution of trigger 'SCOTT.TRIG_NODML'
5)Varray and nested tables
i)
declare
typevmarktype is varray(10) of integer ;
c int;
xvmarktype;
begin
x:=vmarktype(12,45,78,45,77);
c:=x.count;
for iin 1..c loop
dbms_output.put_line(x(i));
endloop;
end;
SQL> @c:\varray.sql
12 /
12
45
78
45
77
PL/SQL procedure successfully completed.

Popular posts from this blog

AGRICULTURAL SCIENTIST RECRUITMENT BOARD

CIVIL SERVICES' (I.A.S.) EXAMINATION

HSC Exam Time Table 2013 Arts, Commerce, Science