Write a Trigger That Logs Payment Changes
Write a Trigger That Logs Payment Changes
Payment Table:
Explanation:
-
CREATE TABLE payment: This command is used to create a new table named
paymentin the database. -
payment_id INT PRIMARY KEY: Defines a column
payment_idthat stores unique integers (IDs) for each payment. It is also set as the primary key, meaning that each payment ID must be unique and cannot be null. -
customer_id INT: This column stores the customer’s ID (integer value) who made the payment.
-
amount DECIMAL(10,2): This column stores the amount paid by the customer in decimal format (maximum 10 digits, 2 of which are after the decimal point).
-
status VARCHAR(50): A column that stores the payment status (like
Pending,Completed, etc.) as a text string of up to 50 characters. -
payment_date DATE: A column that stores the date when the payment was made (in the format
YYYY-MM-DD).
SQL Query:
Step 1: Create payment Table
CREATE TABLE payment (
payment_id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
status VARCHAR(50),
payment_date DATE
);
Step 2: Create payment_log Table
sql
Copy
Edit
CREATE TABLE payment_log (
log_id INT IDENTITY(1,1) PRIMARY KEY,
payment_id INT,
old_amount DECIMAL(10,2),
new_amount DECIMAL(10,2),
old_status VARCHAR(50),
new_status VARCHAR(50),
change_date DATETIME DEFAULT GETDATE()
);
GO
-- Create the trigger that logs updates to the payment table
CREATE TRIGGER trg_log_payment_changes
ON payment
AFTER UPDATE
AS
BEGIN
INSERT INTO payment_log (payment_id, old_amount, new_amount, old_status, new_status)
SELECT
d.payment_id,
d.amount AS old_amount,
i.amount AS new_amount,
d.status AS old_status,
i.status AS new_status
FROM deleted d
INNER JOIN inserted i ON d.payment_id = i.payment_id
WHERE d.amount <> i.amount OR d.status <> i.status;
END;
GO
-- Update the record (this will trigger logging)
UPDATE payment
SET amount = 700.00, status = 'Completed'
WHERE payment_id = 1;
Output:
