Write a business transaction that involves multiple tables and implement it usin

ID: 3557872 • Letter: W

Question

Write a business transaction that involves multiple tables and implement it using PL/SQL. Use at least one Function or Procedure in your code. The transaction may retrieve data from the database and display the result, or it may insert/update data in certain table(s).
Choose a table that you wish to monitor. Create a table to have monitoring data (say LogTable). Create a trigger that automatically records data into the LogTable whenever some tuple is inserted, updated or deleted in the table being monitored.
DON'T GIVE ME SOLUTIONS FOUND ON GOOGLE OR CHEGG.
YOU SHOULD SHOW SCREENSHOT OUTPUT TO RECEIVE CREDIT.

create database sample;//creating database

use sample;//using database

create table emp(eno int ,ename varchar[15],salory int,); // creating tables

create table student(sno int ,sname varchar[15],mat int,phy int,chem int);

begin transaction //transaction starting point

insert into emp values(1001,"raj",9000);// insert records into emp table

insert into emp values(1005,"john",8000);

insert into student values(1,"suraj",90,90,90);

insert into student values(15,"lenin",80,70,90);

delete from emp ;//deletes all records from emp table

select * from emp;//empty table

rollback //going back to startng point of transaction

trigger t //trigger creation

on emp

for [insert|update|delete]

begin

select * from inserted;//inserted,deleted are the magic tables only exists at time of exicution

select * from deleted;

end

//function to accept student no and return the average marks of student from student table

create function fun(@tsno int)

returns int

begin

declare @avg int

select @avg=AVG(mat,phy,chem) from student where sno=tsno;

return @avg;

end

select DBO.fun(1); //DBO=database owner .this statement returns average of student sno=1 i.e 90

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.