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.

Posted in: dbms programs