Question

1. Using Triggers (5 pts.)
   1. Execute the following SQL to create the customer_audit table in the premier schema.

CREATE TABLE IF NOT EXISTS customer_audit (

customer_num CHAR(3) NOT NULL,

  customer_name VARCHAR(35) NOT NULL,

street VARCHAR(15),

city VARCHAR(15),

state CHAR(2),

zip CHAR(5),

credit_limit DECIMAL(8,2),

date_changed DATETIME NOT NULL,

changed_by VARCHAR(45) NOT NULL);

2. Notice that the audit table does not have a primary key defined. Explain why this might be acceptable.

3. Based on the current attributes in the customer_audit table, suggest a possible primary key and explain why it might be unique.

4. Create a trigger that will insert a record into the customer_audit table when the customer data is changed. Only insert a new record if the attributes that are being audited change. The attributes being audited are those that exist in the customer_audit table. For example the customer.balance attribute is not being audited so if it is the only attribute that changes as part of the transaction then we do not need to insert a new record into the audit table. Provide the SQL used to create this trigger.

5. Write an SQL update statement that will change a customer and cause an audit record to be created. Provide the SQL statement you executed for this.

6. Write an SQL statement that will display what is in the customer_audit table. Provide the SQL used.

Answer 1

Q. Notice that the audit table does not have a primary key defined. Explain why this might be acceptable. Answer:

• I notice that Audit table does not have a primary key defined. This is acceptable because sometimes you can have tables without a primary key for storing all records that company wants to knew.
• Basically, A table should have a primary key so that you could identify each row uniquely with it.
• Technically, you can have tables without a primary key, but you'll be breaking good database design rules.

Q. Based on the current attributes in the customer_audit table, suggest a possible primary key and explain why it might be unique. Answer: Based on the current attributes in customer_audit table, I think an only possible primary key is customer_num attribute Because customer number is unique number for all customer.

Q. Create a trigger that will insert a record into the customer_audit table when the customer data is changed. Only insert a new record if the attributes that are being audited change. The attributes being audited are those that exist in the customer_audit table. For example the customer.balance attribute is not being audited so if it is the only attribute that changes as part of the transaction then we do not need to insert a new record into the audit table. Provide the SQL used to create this trigger.

Answer:

CREATE OR REPLACE TRIGGER display_Updation_audit BEFORE DELETE OR INSERT OR UPDATE ON customer_audit FOR EACH ROW WHEN (Customer_num > 0) DECLARE BEGIN dbms_output.put_line('New customer_num: ' || :NEW.customer_num);   dbms_output.put_line('New customer_name: ' || :NEW.customer_name);   dbms_output.put_line('New street: ' || :NEW.street);   dbms_output.put_line('New city: ' || :NEW.city);   dbms_output.put_line('New state: ' || :NEW.state);   dbms_output.put_line('New zip: ' || :NEW.zip);   dbms_output.put_line('New credit_limit: ' || :NEW.credit_limit);   dbms_output.put_line('New date_changed: ' || :NEW.date_changed);     dbms_output.put_line('New changed_by: ' || :NEW.changed_by);    END;

Use of Trigger:

• Maintain Audit of database
• store history for certain action of query

Q. Write an SQL update statement that will change a customer and cause an audit record to be created. Provide the SQL statement you executed for this.

Answer:

• SQL Statement:
• UPDATE customer_audit  SET credit_limit = 5000 WHERE credit_limit > 5000;
• When we execute this query in database then after sometimes customer shopping and they reach their to credit_limit but if customer wants to buy more product then customer have to create new customer_ num for them And that cause an audit record to be created.

Q. Write an SQL statement that will display what is in the customer_audit table. Provide the SQL used.

Answer:

SQL Statement:

• SELECT * FROM customer_audit;
• This will display all the records present in customer_audit.

SQL Use:

• SQL is used for administering, updating, maintaining and manipulating the databases or tables
• SQL is usually used to fetch data, update the records,attributes of the table, or operate on the structure of the database or tables.
• SQL can be used for both relational and multidimensional types of databases.