Hello, DB developers!
I created an update trigger in my SQL Server database. Below is the output of it:
What I don't understand is that everytime they inserted a new data, "Policy number = 23003728 changed" were inserted. Below is my trigger
ALTER TRIGGER [dbo].[MotorECI_UPDATE] ON [dbo].[tblMotorInsurance_eCI]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MotorCINo nvarchar(50)
DECLARE @OldPlateNo nvarchar(50), @NewPlateNo nvarchar(50)
DECLARE @OldEngineNo nvarchar(50), @NewEngineNo nvarchar(50)
DECLARE @OldChassisNo nvarchar(50), @NewChassisNo nvarchar(50)
DECLARE @OldInsuranceType nvarchar(50), @NewInsuranceType nvarchar(50)
DECLARE @OldInsuranceOption nvarchar(50), @NewInsuranceOption nvarchar(50)
DECLARE @OldPeriodFrom date, @NewPeriodFrom date
DECLARE @OldPeriodTo date, @NewPeriodTo date
DECLARE @OldCoverAmount decimal(18,2), @NewCoverAmount decimal(18,2)
DECLARE @OldNetPremium decimal(18,2), @NewNetPremium decimal(18,2)
DECLARE @OldRegistrationFee decimal(18,2), @NewRegistrationFee decimal(18,2)
DECLARE @OldVAT decimal(18,2), @NewVAT decimal(18,2)
DECLARE @OldTotalPremium decimal(18,2), @NewTotalPremium decimal(18,2)
DECLARE @OldAgentID nvarchar(50), @NewAgentID nvarchar(50)
DECLARE @TerritoryLimit nvarchar(50)
DECLARE @OldUsername nvarchar(50), @NewUsername nvarchar(50)
DECLARE @OldBeneficiary nvarchar(250), @NewBeneficiary nvarchar(250)
DECLARE @OldVehicleBrand nvarchar(250), @NewVehicleBrand nvarchar(250)
DECLARE @OldVehicleModel nvarchar(250), @NewVehicleModel nvarchar(250)
DECLARE @OldCompulsoryPrem decimal(18,2), @NewCompulsoryPrem decimal(18,2)
DECLARE @OldCompulsoryNCD nvarchar(250), @NewCompulsoryNCD nvarchar(250)
DECLARE @OldCompulsoryNetPrem decimal(18,2), @NewCompulsoryNetPrem decimal(18,2)
DECLARE @OldOwnPrem decimal(18,2), @NewOwnPrem decimal(18,2)
DECLARE @OldOwnNCD nvarchar(250), @NewOwnNCD nvarchar(250)
DECLARE @OldOwnNetPrem decimal(18,2), @NewOwnNetPrem decimal(18,2)
DECLARE @OldDefenseRecourse decimal(18,2), @NewDefenseRecourse decimal(18,2)
DECLARE @OldPersonalAccident decimal(18,2), @NewPersonalAccident decimal(18,2)
DECLARE @AuditString nvarchar(max)
SELECT *
INTO #TempTable
FROM inserted
WHILE (EXISTS(SELECT MotorCINo FROM #TempTable))
BEGIN
SET @AuditString = ''
SELECT
@MotorCINo = MotorCINo,
@NewPlateNo = VehiclePlateNo,
@NewEngineNo = VehicleEngineNo, @NewChassisNo = VehicleChasisNo,
@NewInsuranceType = InsuranceType, @NewInsuranceOption = InsuranceOption,
@NewPeriodFrom = PeriodFrom, @NewPeriodTo = PeriodTo,
@NewCoverAmount = CoverAmount,
@NewNetPremium = NetPremium, @NewRegistrationFee = RegistrationFee, @NewVAT = VAT, @NewTotalPremium = TotalPremium,
@NewAgentID = AgentID, @NewUsername = Username,
@NewBeneficiary = Beneficiary, @NewVehicleBrand=VehicleBrand, @NewVehicleModel=VehicleModel, @NewCompulsoryPrem=CompulsoryPrem, @NewCompulsoryNCD=CompulsoryNCD, @NewCompulsoryNetPrem=CompulsoryNetPrem,
@NewOwnPrem = OwnPrem, @NewOwnNCD=OwnNCD, @NewOwnNetPrem=OwnNetPrem, @NewDefenseRecourse=DefenseRecourse, @NewPersonalAccident=PersonalAccident
FROM #TempTable
SELECT
@OldPlateNo = VehiclePlateNo,
@OldEngineNo = VehicleEngineNo, @OldChassisNo = VehicleChasisNo,
@OldInsuranceType = InsuranceType, @OldInsuranceOption = InsuranceOption,
@OldPeriodFrom = PeriodFrom, @OldPeriodTo = PeriodTo, @OldCoverAmount = CoverAmount,
@OldNetPremium = NetPremium, @OldRegistrationFee = RegistrationFee, @OldVAT = VAT, @OldTotalPremium = TotalPremium,
@OldAgentID = AgentID, @OldUsername = Username,
@OldBeneficiary = Beneficiary, @OldVehicleBrand=VehicleBrand, @OldVehicleModel=VehicleModel, @OldCompulsoryPrem=CompulsoryPrem, @OldCompulsoryNCD=CompulsoryNCD, @OldCompulsoryNetPrem=CompulsoryNetPrem,
@OldOwnPrem = OwnPrem, @OldOwnNCD=OwnNCD, @OldOwnNetPrem=OwnNetPrem, @OldDefenseRecourse=DefenseRecourse, @OldPersonalAccident=PersonalAccident
FROM deleted WHERE MotorCINo=@MotorCINo
SET @AuditString = 'Policy number = ' + @MotorCINo + ' changed'
if(@OldPlateNo <> @NewPlateNo)
Set @AuditString = @AuditString + ' plate number from ' + @OldPlateNo + ' to ' + @NewPlateNo
if(@OldEngineNo <> @NewEngineNo)
Set @AuditString = @AuditString + ' engine number from ' + @OldEngineNo + ' to ' + @NewEngineNo
if(@OldChassisNo <> @NewChassisNo)
Set @AuditString = @AuditString + ' chassis number from ' + @OldChassisNo + ' to ' + @NewChassisNo
if(@OldInsuranceType <> @NewInsuranceType)
Set @AuditString = @AuditString + ' insurance type from ' + @OldInsuranceType + ' to ' + @NewInsuranceType
if(@OldInsuranceOption <> @NewInsuranceOption)
Set @AuditString = @AuditString + ' insurance option from ' + @OldInsuranceOption + ' to ' + @NewInsuranceOption
if(@OldPeriodFrom <> @NewPeriodFrom)
Set @AuditString = @AuditString + ' inception date from ' + CAST(@OldPeriodFrom AS nvarchar(20)) + ' to ' + CAST(@NewPeriodFrom AS nvarchar(20))
if(@OldPeriodTo <> @NewPeriodTo)
Set @AuditString = @AuditString + ' inception date from ' + CAST(@OldPeriodTo AS nvarchar(20)) + ' to ' + CAST(@NewPeriodTo AS nvarchar(20))
if(@OldCoverAmount <> @NewCoverAmount)
Set @AuditString = @AuditString + ' cover amount from ' + CAST(@OldCoverAmount AS nvarchar(20)) + ' to ' + CAST(@NewCoverAmount AS nvarchar(20))
if(@OldNetPremium <> @NewNetPremium)
Set @AuditString = @AuditString + ' net premium from ' + CAST(@OldNetPremium AS nvarchar(20)) + ' to ' + CAST(@NewNetPremium AS nvarchar(20))
if(@OldRegistrationFee <> @NewRegistrationFee)
Set @AuditString = @AuditString + ' registry fee from ' + CAST(@OldRegistrationFee AS nvarchar(20)) + ' to ' + CAST(@NewRegistrationFee AS nvarchar(20))
if(@OldVAT <> @NewVAT)
Set @AuditString = @AuditString + ' vat from ' + CAST(@OldVAT AS nvarchar(20)) + ' to ' + CAST(@NewVAT AS nvarchar(20))
if(@OldTotalPremium <> @NewTotalPremium)
Set @AuditString = @AuditString + ' total premium from ' + CAST(@OldTotalPremium AS nvarchar(20)) + ' to ' + CAST(@NewTotalPremium AS nvarchar(20))
if(@OldAgentID <> @NewAgentID)
Set @AuditString = @AuditString + ' agent code from ' + @OldAgentID + ' to ' + @NewAgentID
if(@OldBeneficiary <> @NewBeneficiary)
Set @AuditString = @AuditString + ' beneficiary from ' + @OldBeneficiary + ' to ' + @NewBeneficiary
if(@OldVehicleBrand <> @NewVehicleBrand)
Set @AuditString = @AuditString + ' MAKE from ' + @OldVehicleBrand + ' to ' + @NewVehicleBrand
if(@OldVehicleModel <> @NewVehicleModel)
Set @AuditString = @AuditString + ' MODEL from ' + @OldVehicleModel + ' to ' + @NewVehicleModel
if(@OldCompulsoryPrem <> @NewCompulsoryPrem)
Set @AuditString = @AuditString + ' compulsory premium from ' + CAST(@OldCompulsoryPrem as nvarchar(20)) + ' to ' + cast(@NewCompulsoryPrem as nvarchar(20))
if(@OldCompulsoryNCD <> @NewCompulsoryNCD)
Set @AuditString = @AuditString + ' compulsory NCD from ' + @OldCompulsoryNCD + ' to ' + @NewCompulsoryNCD
if(@OldCompulsoryNetPrem <> @NewCompulsoryNetPrem)
Set @AuditString = @AuditString + ' compulsory net premium from ' + CAST(@OldCompulsoryNetPrem as nvarchar(20)) + ' to ' + CAST(@NewCompulsoryNetPrem as nvarchar(20))
if(@OldOwnPrem <> @NewOwnPrem)
Set @AuditString = @AuditString + ' comprehensive premium from ' + CAST(@OldOwnPrem as nvarchar(20)) + ' to ' + CAST(@NewOwnPrem as nvarchar(20))
if(@OldOwnNCD <> @NewOwnNCD)
Set @AuditString = @AuditString + ' comprehensive NCD from ' + @OldOwnNCD + ' to ' + @NewOwnNCD
if(@OldOwnNetPrem <> @NewOwnNetPrem)
Set @AuditString = @AuditString + ' comprehensive net premium from ' + CAST(@OldOwnNetPrem as nvarchar(20)) + ' to ' + CAST(@NewOwnNetPrem as nvarchar(20))
if(@OldDefenseRecourse <> @NewDefenseRecourse)
Set @AuditString = @AuditString + ' defense and recourse from ' + CAST(@OldDefenseRecourse as nvarchar(20)) + ' to ' + CAST(@NewDefenseRecourse as nvarchar(20))
if(@OldPersonalAccident <> @NewPersonalAccident)
Set @AuditString = @AuditString + ' personal accident from ' + CAST(@OldPersonalAccident as nvarchar(20)) + ' to ' + CAST(@NewPersonalAccident as nvarchar(20))
insert into tblMotorInsuranceECI_Logs values(@MotorCINo, @AuditString, GETDATE())
-- Delete the row from temp table, so we can move to the next row
Delete from #TempTable where MotorCINo = @MotorCINo
END
END
Is it not possible to insert only the updated policies? Thanks for giving me clarification.