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

Introduction to JavaScript- Basics

JavaScript is the most popular scripting language on the internet, and works in all major browsers, such as Internet Explorer, Firefox, Chrome, Opera, and Safari. What You Should Already Know Before you continue you should have a basic understanding of the following: HTML and CSS If you want to study these subjects first, find the tutorials on our Languages page . What is JavaScript? JavaScript was designed to add interactivity to HTML pages JavaScript is a scripting language A scripting language is a lightweight programming language JavaScript is usually embedded directly into HTML pages JavaScript is an interpreted language (means that scripts execute without preliminary compilation) Everyone can use JavaScript without purchasing a license Are Java and JavaScript the same? NO! Java and JavaScript are two completely different languages in both concept and design! Java (developed by Sun Microsystems) is a powerful and much more complex programming language ...

IBM Sample Problem Using Speed

Question 1 A policeman starts chasing a thief 30 minutes after the thief had run from a spot. With an average speed of 20km per hour, he takes 2 hours to catch the thief. What is the average speed of the thief? a)16km/hr b)25km/hr c)24km/hr d)18km/hr Answer : a)16km/hr Solution: As given, the average speed of the policeman = 20km/hr. He takes 2 hours to catch the thief, so from formula, "distance = speed x time" we have The total distance covered by the police to catch the thief = 20 x 2 = 40 km (This value is also equal to the distance run by thief before being caught by Police.) Policeman had started late by 30 minutes and took 2 hours to catch the running thief. Above means that the thief takes (30minutes + 2 hours =) 5/2 hours to reach 40km. So the speed of the thief = 40/(5/2) = 40 x 2 / 5 = 16 km/hr. Hence the answer is 16km/hr. Question 2 From a particular spot, Tom started to chase Jerry which had left the spot before 30 minutes. Tom ran acro...

MCA - Syllabus, Notes, Question Papers, Projects

MCA - Syllabus, Notes, Question Papers, Projects : SEMESTER - 1 Syllabus: Syllabus PDF Notes: Semester 1 Notes Question Papers:  Project: SEMESTER - 2 Syllabus: Syllabus PDF                                   Notes: Semester 2 Notes Question Papers:  Projects:  SEMESTER - 3  Syllabus: Syllabus PDF                                   Notes: Semester 3 Notes Question Papers:  Project: SEMESTER - 4  Syllabus: Syllabus PDF                               ...