CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
schwifty
schwifty

Reputation: 185

Creating a trigger to store an audit trail

I have to create a trigger on the Claims table that is triggered whenever a new record is inserted into the Claims table. This trigger should store the customer name and the total amount_of_claim by that customer.

Claim_audits (audit table) have already been created.

Schema:

Claims

id int(11)
status_id int(11)
customer_policy_id int(11)
date_of_claim date
amount_of_claim float

> one or many to one(and only one) towards Customer_policy

Customer_policy

id int(11)
policy_start_date date
policy_renewal_date date
policy_id int(11)
customer_id int(11)
agent_id(11)

> one or many to one (and only one) towards Customer

Customer

id int(11)
first_name varchar(30)
last_name varchar(30)
email varchar(30)
address_id int(11)

Output should look like this:

customer_name   amount_of_claim
abhinav         195000

This is what I have tried:

CREATE TRIGGER claim_audits on claims 
for insert
as
    declare @custname varchar(25);
    declare @amount varchar(25);
    declare @action varchar(25);
    
    select @custname = first_name from customer c
    join inserted i on i.id=c.id;
    select @amount = i.amount_of_claim from inserted i;
    select @action = 'Updated customer claimed amount';
    
    insert into claim_audits values(@custname , @amount , @action);
    select * from claim_audits; 
go

Upvotes: 1

Views: 1105

Answers (1)

Dale K
Dale K

Reputation: 27226

The Inserted pseudo-table can have 0-N rows, and you need to handle that. And as with anything SQL related you should approach it using a set-based approach - not a procedural approach.

You also don't appear to have been obtaining the customer id correctly - at least based on your table definitions. I must say, its very odd to be storing the first name of the customer in your audit table. Why not store the customer id? The name is not unique, so you haven't provided a reliable audit trail.

create trigger claim_audits
on claims
for insert
as
begin
    set nocount on;

    insert into dbo.claim_audits (custname, amount, [action])
        select C.first_name, I.amount_of_claim, 'Updated customer claimed amount'
        from Inserted I
        inner join Customer_Policy CP on CP.id = I.customer_policy_id
        inner join Customer C on C.id = CP.customer_id;
end;

Note - you do not want to be attempting to return data from a trigger.

And as pointed out by @Squirral: amount_of_claim float: float is an approximate value and should never be used for money. Use decimal or numeric instead.

Upvotes: 2

Related Questions