Skip to main content

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

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

The Union Public Service Commission (U.P.S.C.)  conducts Civil Services' Examination once a year in two stages. The Preliminary Examination (Objective Type) for selection of candidates for the Main Examination is held in the month of May. The Civil Services Main Examination  is held in the months of October/November. Blank application forms and other particulars are published in the Employment News, generally in the month of December. The last date for the submission of applications to the Secretary, Union Public Service Commission, Dholpur House, Shahjahan Road, NewDelhi-11001 1 is usually the last week of January of the year of examination. The Combined Civil Services Examination is conducted for Recruitment to the following Services/Posts: 1. Indian Administrative Service. 2. Indian Foreign Service. 3. Indian Police Service. 4. Indian P & T Accounts & Finance Service, Group 'A'. 5. Indian Audit and Accounts Service, Group 'A'. 6. Indian Customs and Centr...

(B) Basic Question in .net(dotNet) What is a IL?

(B)What is a IL? Twist :- What is MSIL or CIL , What is JIT? (IL)Intermediate Language is also known as MSIL (Microsoft Intermediate Language) or CIL (Common Intermediate Language). All .NET source code is compiled to IL. This IL is then converted to machine code at the point where the software is installed, or at run-time by a Just-In- Time (JIT) compiler.

Placement Guide

Placement Guide Posts are Under Following Topics If You Have Any Questions : Contact us 1. Recruitment Patterns Of Various Companies Recruitment Patters 2. Interview Questions :- On various topics : C C++ Java Advanced Java DBMS Data Structure Operating System Web Technologies 3. Aptitude Tests (Practice Test Papers on various Topics) : Quantitative Aptitude 4. Placement Papers : (Previous Years Aptitude Papers) - of various Companies : Accenture Amdocs Capgemini Cognizant Deloitte Government Job HCL IBM infosys microsoft Oracle Persistent TCS Tech Mahindra     Learn Languages Here : Important Programming Languages  If You Have Any Questions : Contact us