Creating a trigger to store an audit trail

Question

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

The first thing you need to do is read about the Inserted and Deleted pseudo-tables, because they can have 0-N rows... not just 1. And what have you noticed not working about your trigger? And why are you selecting * from claim_audits at the end? Its very bad practice to select within a trigger... what are you trying to accomplish. — Dale K
– Dale K, Commented Mar 25, 2021 at 7:59
@DaleK but won't this be only triggered after insert query so it will always have atleast one record?? — schwifty
– schwifty, Commented Mar 25, 2021 at 8:01
amount_of_claim float float is approximate value. Use decimal or numeric instead. — Squirrel
– Squirrel, Commented Mar 25, 2021 at 8:09
@ShubhamPanwar also putting the total claim amount if the client has multiple claims, is in my opinion meaningless. But I guess it really comes down to, what are you using this audit table for? — Dale K
– Dale K, Commented Mar 25, 2021 at 8:38

Dale K · Accepted Answer · 2021-03-25 08:10:15Z

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.

Collectives™ on Stack Overflow

Creating a trigger to store an audit trail

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related