trigger program in vb

AIM:

To write a program in sql for executing trigger using sql commands.

ALGORITHM:

1. Start the process.

2. Create a table account and the fields account number,name,branch name and balance.

3. Insert the values into the table named account.

4. Create a trigger named trig.

5. The trigger is worked while we insert,delete and updating the fields.

6. Create a trigger for the table account on each row modification.

7. If we access the account on Saturday and Sunday trigger is printed.

8. Create a trigger name trig1.

9. If we want to delete the account trigger is printed.

10. Create a trigger named trig2.

11. If we want to update the rows (i.e) marks trigger is printed.

12. Stop the process.

SYNTAX:

Create [or replace] trigger<trigger name>

Before/after triggering event or<table name>

[For each row]

[When condition]

Declare

Declaration statements;

Begin

Executable statements;

Exception

Exception handling statements;

End;

TABLE CREATION:

Create table account[accno number(3),name varchar(10),branch_namevarchar(10),bal_number(10,2));

TO INSERT THE VALUES INTO THE TABLE:

Insert into accvalues(&accno,’&name’,’&branch name’,bal);

PROGRAM CODING:

INSERTION:

Create or replace triggering before insert or delete or update on acc for each row

Begin

If to_char(sysdate,’dy’)=’sun’ then

Raise_application_error(-2000 1,’try on any weekdays’);Else

Dbms_output.put_line(‘welcome back’);

End if;

End;

DELETION:

Create or replace trigger manju

Before delete on account

Begin

Raise_application_error(-2000 2,’no deletion’);

End;

UPDATION:

create or replace trigger manju

before update on account

for each row

begin

if old bad>250 then

update.account set bal=new.bal

else

raise.application_error(-20004,’low balance’);end if;

end;

OUTPUT:

TABLE CREATED.

INSERT INTO ACCOUNT VALUES(&ACCNO,’&NAME’,’&BRANCHNAME’,&BAL);

ENTER VALUE FOR ACCNO:10

ENTER VALUE FOR NAME:ISHU

ENTER VALUE FOR BRANCHNAME:CBE

ENTER VALUE FOR BAL:20000

OLD1:INSERT INTO ACCOUNT VALUES(&ACCNO,’&NAME’,’&BRANCHNAME’,&BAL)

NEW1:INSERT INTO ACCOUNT VALUES(10,’ISHU’,’CBE’,20000)

1 ROW CREATED.

ACCNO	NAME	BRANCH NAME	BAL
10	ISHU	CBE	20000

INSERTION:

WELCOME BACK

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

DELETION:

TRIGGER CREATED SUCCESSFULLY.

UPDATION:

TRIGGER UPDATED SUCCESSFULLY.

Read more »

programs in sql and DBMS cse department programs

CURSOR

AIM:

      To write a program in sql for implementing the concept of cursor.

ALGORITHM:

 FOR % FOUND:

1. Start the program.

2. Create the table student and insert values into the tables.

3. Declare the cursor named ”a”.

4. In the loop fetch the cursor “a” into student roll no from the student.

5. Update the student table in the mark2 fields.

6. Set marks in the corresponding sno.

7. Exit from the loop when the mark is updated.

8. Print the line “table updated”.

9. Close the cursor “a”.

10. Stop the process.

FOR  % ROW COUNT:

1. Start the process.

2. Row count the number of rows in the student name.

3. Declare a cursor “b”.

4. Open the cursor.

5. In the loop fetch the cursor “b” into sno.

6. Count the no of rows in the student table.

7. Exit from the loop after the rollno is counting.

8. Print the line "fetched the no of rows into the student table”.

9. Close the cursor.

10. Stop the process.

FOR % IS OPEN:

1. Start the process.

2. Check whether the cursor is opened or closed.

3. Declare a cursor named “c”.

4. Check the condition if the cursor is open or not using % is open.

5. If it is not open print “cursor is not yet opened”.

6. Open the cursor.

7. If it is open print “cursor is opened now.

8. Close the cursor.

9. Stop the process.

SYNTAX:

Cursor<cursor name>is<select statement>

OPEN:

Open<cursor name>;

FETCH:

Fetch<cursor name>into variable list,record name.

CLOSE:

Close<cursor name>;

CURSOR FOR LOOP:

For<record name>in<cursor name>loop……………loop statements.

End loop:

CURSOR WITH UPDATE:

Cursor<cursor name>is select<column name>from<table name>

[where condition]

For update[of column names][no wait]

TABLE CREATION:

Create table student (sno number(5),sname varchar2(10),mark1 number(3),mark2 number(3));

TO INSERT THE VALUES INTO THE TABLE:

Sql>insert into student values(&sno,’&sname’,&mark1,&mark2);

 PROGRAM CODING FOR % FOUND:

Declare

Sno student.sno %type;

Cursor a is select sno from student where sno=2;

Begin

Open a;

Loop

Fetch a into sno;

Update student set mark2=75 where sno=2;

Exit when a %-not found;

End loop;

Dbms_output.put_line(‘table updated’);

Close a;

End;

PROGRAM CODING FOR  % ROW COUNT:

Declare

Cursor b is select *from student where mark1>60;

Sno student %row type;

Begin

Open b;

Loop

Fetch b into sno;

Exit when b % not found;

End loop;

Dbms_output.put_line(‘fetched’//b %row count//’from table’);

Close b;

End;

PROGRAM CODING FOR % IS OPEN:

Declare

Cursor c is select *from student;

Begin

If not c% is open then

Dbms_output.put_line(‘cursor is not yet opened’);

End if;

Open c;

If c% is open then

Dbms_output.put_line(‘cursor is open now’);

End if;

Close c;

End;

OUTPUT:

FOR % FOUND:

Table  created.

Sql>select *from student;

Sno	Sname	Mark1	Mark2
1	Rose	196	186
2	Jas	175	173
3	Maga	165	156
4	Tamil	198	179

Row count.

FOR %ROW COUNT:

Fetched 4 to from table

Pl/sql procedure successfully  completed.

FOR %ISOPEN:

Cursor is not yet opened.

Cursor is open now

Pl/sql procedure successfully completed.

   

RESULT:

   Thus the cursor commands are executed successfully.

Read more »