Write the appropriate pl/sql codes that allow the different the following tasks:

Question

Write the appropriate pl/sql codes that allow the different the following tasks:

1. Create three tables
• Account: account_id, cust_id,amount,creation_date
• Customer:cust_id,cust_name,cust_mobile,cust_birthdate
• Employee: emp_id,emp_name,emp_salary

5. Create a trigger which records all changes (insert, update, delete) made on the table
account in a new table called account_history ()
6. Create a trigger which does not accept an amount of less than 0 for operations of insert or
update on the table account
7. Create three users : usr1, usr2 and usr3:
a. Usr1 and usr2 belong to a role named hr. In this role, users can execute all procedures
and functions in the package but cannot read and modify the content of the three
tables.
b. Usr3 can only update the cust_mobile in the table customer. He can also give his
privileges to other users.
II. Let us suppose that the table account contains the following rows:

a. Write a well-formed XML document "account.xml" to describe the content of the
account table.
b. Write the DTD file "account.dtd" that should be used to validate the previous XML
document.

Explanation / Answer

CREATE TABLE Account(
account_id number(9),
amount number(9,2),
creation_date date);

CREATE TABLE Customer(
cust_id number(9),
cust_name varchar2(25),
cust_mobile number(11),
cust_birthdate date);

CREATE TABLE Employee(
emp_id number(9),
emp_name varchar2(25),
emp_salary number(9,2));

CREATE OR REPLACE TRIGGER account_after_insert
AFTER INSERT
ON Account
FOR EACH ROW
BEGIN
INSERT INTO account_history
( account_id,
amount,
creation_date,
date)
VALUES
( :new.account_id,
:new.amount,
:new.creation_date,
sysdate());
END;

CREATE OR REPLACE TRIGGER account_after_update
AFTER UPDATE
ON Account
FOR EACH ROW
BEGIN
INSERT INTO account_history
( account_id,
amount,
creation_date,
date)
VALUES
( :new.account_id,
:new.amount,
:new.creation_date,
sysdate());
END;

CREATE OR REPLACE TRIGGER account_after_delete
AFTER Delete
ON Account
FOR EACH ROW
BEGIN
INSERT INTO account_history
( account_id,
amount,
creation_date,
date)
VALUES
( :old.account_id,
:old.amount,
:old.creation_date,
sysdate());
END;

CREATE OR REPLACE TRIGGER account_before_insert
BEFORE INSERT
ON Account
FOR EACH ROW
BEGIN
IF :new.amount > 0 THEN
INSERT INTO account
( account_id,
amount,
creation_date)
VALUES
( :new.account_id,
:new.amount,
:new.creation_date);
   ELSE
   PRINT "Amount is less than 0"
   END IF;
END;

CREATE OR REPLACE TRIGGER account_before_update
BEFORE UPDATE
ON Account
FOR EACH ROW
BEGIN
IF :new.amount > 0 THEN
UPDATE account SET
amount = :new.amount
   WHERE :old.account_id = :new.account_id
   ELSE
   PRINT "Amount is less than 0"
   END IF;
END;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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