ONLY DO THIS USING SQL DEVELOPER ORACLE SQL And using the fo

ONLY DO THIS USING SQL DEVELOPER (ORACLE SQL).

And using the following tables (Tables for Part 1 and 2):

CREATE TABLE Customers
(
Cust_id int PRIMARY KEY,
Last_Name varchar(255),
First_Name varchar(255),
State varchar(255),
Zipcode Number(10)
);

CREATE TABLE Customers_history
(
History_id int PRIMARY KEY,
Cust_id int,
Last_Name varchar(255),
First_Name varchar(255),
State varchar(255),
Zipcode Number(10)
);

TEST CODE BEFORE POSTING SOLUTION.

Your assignment is to create an auditing policy that keep track of insertion and update activities of Customers table. This Customers trigger fires before update or insert or delete on customers. You have to test your trigger and provide screenshots to prove that it works correctly

D1. Create a new table named Customers. The new table should have at least 5 columns including a primary key. (Cust-id, First_name, Last_name, State, Zip-code. Use cust_id as PK.

D2. Insert 2 rows into the Customers table.

D3. Create a history table for Customers table, (create table customers_history). The table should have same structure as Customers table Cust-id + First_name + Last_name+ State + Zip_code. Don\'t forget the history_id as pk . Do NOT insert any data (values) into this table.

D4. Create a trigger for auditingcreate trigger audit_customers on the Custoners table

D5. Insert 3 records into the Customers table.

D4. Retrieve all rows from customers and customers-history table to verify that rows were added to the Customers table and that the trigger is populating the customers-history table.

D5. Now update the zip code of one customer and check if rows were added to the customers table and that the trigger did populate the customers-history table with the new zip code.

D6. Update the first record of Customers, and then delete this record.

D7. Display the content of Customers table and the history table.

You have to provide the trigger code, code you used to create the table and insert records. (2 points)

Solution

/** [D1]**/
CREATE TABLE Customers (
Cust_id Number (8),
First_name varchar2(20),
Last_name varchar2(20),
State varchar2 (20),
Zip_code Number (9),
Primary key (Cust_id)
);

/***[D2]***/
Insert into Customers values ( 1, \'David\',\'Smith\', \'Virginia\', 300050);
Insert into Customers values ( 2, \'Vinsel\', \'Nalla\', \'NYK\', 300051);

/**[D3]**/
CREATE TABLE Customers_history (
history_id Number (8) ,
Cust_id Number (8),
First_name varchar2(20),
Last_name varchar2(20),
State varchar2 (20),
Zip_code Number (9),
Primary key (history_id)
);

/**** [D4]*********
I have used MErge concept for this.
Trigger invoke for each delete , update , insert statement on
Clients Table.
**************************/

create or replace
TRIGGER audit_Customers
BEFORE INSERT OR UPDATE OR DELETE ON Customers

BEGIN
MERGE INTO Customers_history d
USING Customers s
ON (d.Cust_id = s.Cust_id)

WHEN MATCHED THEN
UPDATE SET d.Zip_code = s.Zip_code

WHEN NOT MATCHED THEN
Insert (d.History_id,d.Cust_id , d.First_name , d.Last_name , d.State ,d.Zip_code )
values (s.Cust_id, s.Cust_id , s.First_name , s.Last_name , s.State ,s.Zip_code);

END audit_Customers;

/** Once you complie the trigger check it there is Error or not.If you found error then open trigger and add semicolon at the end of the trigger.***/

/*** [D5] ******/
Insert into Customers values ( 3, \'Thersa\', \'john\', \'Londan\', 300152);
Insert into Customers values ( 4, \'rahul\', \'modi\', \'delhi\', 300153);
Insert into Customers values ( 5, \'aamir\', \'khan\', \'Iran\', 300154);

/**** [D6] ******/

UPDATE Customers
SET Zip_code = 123456
WHERE Cust_id = 1;

DELETE FROM Customers
WHERE Cust_id = 1;

/******** [D7] ********/

SELECT * FROM Customers;
SELECT * FROM Customers_history;