It\'s PL/SQL Thanks for helping me with this code. The Clients trigger fires bef

Question

It's PL/SQL

Thanks for helping me with this code.

The Clients trigger fires before update or insert or delete on customers. Test your trigger and provide screenshots to prove that it works correctly

CREATE TABLE Clients (

Cl-id Number (8),

F_name varchar2(20),

L_name varchar2(20),

State varchar2 (20),

Zip_code Number (9),

Primary key (Cl-id)

);

Insert into Clients values ( 6, ‘David’, ‘Smith’, ‘Virginia’. 300050);

Insert into Clients values ( 8, ‘David’, ‘Smith’, ‘Virginia’. 300150);

CREATE TABLE Clients_Hitory (

History-id Number (8),

Cl-id Number (8),

F_name varchar2(20),

L_name varchar2(20),

State varchar2 (20),

Zip_code Number (9),

Primary key (History-id)

);

1. Create a trigger for auditing—create trigger audit_clients on the Clients table

2. Insert 3 records into the Clients table.

3. Retrieve all rows from clients and clients-history table to verify that rows were added to the Clients table and that the trigger is populating the clients-history table.

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

5. Update the first record of Clients, and then delete this record.

6. Display the content of Clients table and the history table.

Explanation / Answer

/************ Table 1 ***********/
CREATE TABLE Clients (
Cl_id Number (8),
F_name varchar2(20),
L_name varchar2(20),
State varchar2 (20),
Zip_code Number (9),
Primary key (Cl_id)
);

/*************** Table 2 ****************/
CREATE TABLE Clients_Hitory (
History_id Number (8) AUTO_INCREMENT,
Cl_id Number (8),
F_name varchar2(20),
L_name varchar2(20),
State varchar2 (20),
Zip_code Number (9),
Primary key (History_id)
);

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

Insert into Clients values ( 1, 'David','Smith', 'Virginia', 300050);
Insert into Clients values ( 2, 'Vinsel', 'Nalla', 'NYK', 300051);

create or replace
TRIGGER audit_clients
BEFORE INSERT OR UPDATE OR DELETE ON Clients

BEGIN
MERGE INTO Clients_Hitory d
USING Clients s
ON (d.Cl_id = s.Cl_id)

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

WHEN NOT MATCHED THEN
Insert (d.History_id,d.Cl_id , d.F_name , d.L_name , d.State ,d.Zip_code )
values (s.cl_id, s.Cl_id , s.F_name , s.L_name , s.State ,s.Zip_code);

END audit_clients;

Insert into Clients values ( 3, 'Thersa', 'john', 'Londan', 300152);

SELECT * FROM Clients;
SELECT * FROM Clients_Hitory;

UPDATE Clients
SET Zip_code = 123456
WHERE Cl_id = 1;

SELECT * FROM Clients;
SELECT * FROM Clients_Hitory;

DELETE FROM Clients
WHERE Cl_id = 2;

SELECT * FROM Clients;
SELECT * FROM Clients_Hitory;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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