Create a table called Customer_Archives that has the same columns as customers t

Question

Create a table called Customer_Archives that has the same columns as customers table and it also has a column that is called “Deletion_Date”

Customers colum has this atribute

(Customer_id Number(10),
Customer_FirstName Varchar(50),
Customer_LastName Varchar(50),
Customer_Address Varchar(255),
Customer_City Varchar(50),
Customer_State Varchar (2),
Customer_Zip Varchar (20),
Customer_Phone Varchar (30),
Customer_Fax Varchar (30)

Create a Trigger on table Customers so that every time a record is deleted from table customers this same record is inserted in table Customer_Archives with the current date as Deletion_Date

Now insert a new customer into table Customers and then delete it. Is the record inserted correctly into table Customers_Archive?

Explanation / Answer

Trigger Creation and Execution:
____________________________
SQL> create or replace trigger cust_trigger
2 before delete on Customers
3 for each row
4 declare
5 ar_row Customers%rowtype;
6 begin
7 insert into Customers_Archives values(ar_row.Customer_id,ar_row.Customer_FirstName,ar_row.Customer_LastName,ar_row.Customer_Address,ar_row.Customer_City,ar_row.Customer_State,ar_row.Customer_Zip ,ar_row.Customer_Phone,ar_row.Customer_Fax ,sysdate());
8 dbms_output.put_line('New row is added to Customers_Archive Table with Customer_ID:' ||ar_row.Customer_id ||'on date:' || sysdate());
9 end;
10 /

Trigger created.

SQL> delete from Customers where Customer_id = 204;
New row is added to Customers_Archive Table with Customer_ID:204 on date:02-MAR-17

1 row deleted.

Customers Table:
_______________

SQL> create table Customers(Customer_id Number(10),Customer_FirstName Varchar(50),Customer_LastName Varchar(50),Customer_Address Varchar(255),Customer_City Varchar(50),Customer_State Varchar (2),Customer_Zip Varchar (20),Customer_Phone Varchar (30),Customer_Fax Varchar (30));

Table created.

SQL> desc Customers;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER(10)
CUSTOMER_FIRSTNAME VARCHAR2(50)
CUSTOMER_LASTNAME VARCHAR2(50)
CUSTOMER_ADDRESS VARCHAR2(255)
CUSTOMER_CITY VARCHAR2(50)
CUSTOMER_STATE VARCHAR2(2)
CUSTOMER_ZIP VARCHAR2(20)
CUSTOMER_PHONE VARCHAR2(30)
CUSTOMER_FAX VARCHAR2(30)

Customers_Archives Table:
__________________

SQL> create table Customers_Archives(Customer_id Number(10),Customer_FirstName Varchar(50),Customer_LastName Varchar(50),Customer_Address Varchar(255),Customer_City Varchar(50),Customer_State Varchar (2),Customer_Zip Varchar (20),Customer_Phone Varchar (30),Customer_Fax Varchar (30),Deletion_Date date);

Table created.

SQL> desc Customers_Archives;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID     NUMBER(10)
CUSTOMER_FIRSTNAME VARCHAR2(50)
CUSTOMER_LASTNAME VARCHAR2(50)
CUSTOMER_ADDRESS VARCHAR2(255)
CUSTOMER_CITY VARCHAR2(50)
CUSTOMER_STATE VARCHAR2(2)
CUSTOMER_ZIP VARCHAR2(20)
CUSTOMER_PHONE VARCHAR2(30)
CUSTOMER_FAX VARCHAR2(30)
DELETION_DATE DATE

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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