update table fieds based on trigger inserted
 

In a project, user will key in data in a software and data will be export into SQL Server 2008 R2

Once SQL Server identify there is an inserted data (new row), it will trigger code to update table Abbyy base on business rule.

In table Abbyy, its contain column

My business rule requirement is

A1 Engine No doest not exist in ABC System
A2 Registration No does not exist in ABC System
A3 Engine No and Registration No does not matched with each other
A4 Engine No has been repeated more than twice
A5 Date of Service More than Processing Date 7 months


EngineNo must exist in database.

For previous records, EngineNo with CouponStatus='Approve' is count and should ignore CouponStatus='Reject'.

If EngineNo with CouponStatus='Approve' repeated more than twice then Update Table Abbyy CouponStatus = 'Reject', RejectCode = 'A4'

How should I code in trigger for business rule A4 so it only check CouponStatus='Approve' and ignore CouponStatus='Reject'.

Here is my trigger code

Re: update table fieds based on trigger inserted
 

[DocID] [varchar](20) NOT NULL,
[FormNo] [varchar](20) NULL,
[DateOfReceive] [varchar](10) NULL,
[ActualSubmit] [int] NULL,
[ServiceType] [varchar](10) NULL,
[ProcessingDate] [date] NULL,
[CustName] [varchar](50) NULL,
[CustPhoneNo] [varchar](12) NULL,
[EngineNo] [varchar](15) NULL,
[VehRegNo] [varchar](10) NULL,
[Mileage] [int] NULL,
[DateOfService] [date] NULL,
[DealerCode] [char](10) NULL,
[CouponStatus] [varchar](10) NULL, **(only accept 'Approve' OR 'Reject')**
[RejectCode] [varchar](10) NULL, **(only accept null, 'A1', 'A2', 'A3', 'A4', & 'A5')**

Re: update table fieds based on trigger inserted
 

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[BusinessRule]
ON [dbo].[Abbyy]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;


DECLARE @Identity varchar(225);
DECLARE @RegNo varchar(225);
DECLARE @ProDate date;
DECLARE @SerDate date;
DECLARE @PriKey varchar(255);
DECLARE @CouStatus varchar(255);

SELECT @Identity=EngineNo, @RegNo=VehRegNo, @ProDate=ProcessingDate, @SerDate=DateOfService, @PriKey=DocID, @CouStatus=CouponStatus FROM Inserted


IF EXISTS (Select EngineNo
From Abbyy
Where
NOT EXISTS
(Select EngineNo
From eDaftarOwnerDetail
where eDaftarOwnerDetail.EngineNo = @Identity))

UPDATE Abbyy
SET CouponStatus = 'Reject', RejectCode = 'A1'
WHERE EngineNo = @Identity
and DocID=@PriKey

Else If EXISTS (Select VehRegNo
From Abbyy
Where
NOT EXISTS
(Select VehRegNo
From eDaftarOwnerDetail
Where eDaftarOwnerDetail.VehRegNo = @RegNo))
UPDATE Abbyy
SET CouponStatus = 'Reject', RejectCode = 'A2'
WHERE VehRegNo = @RegNo
and DocID=@PriKey

Else If EXISTS (Select EngineNo, VehRegNo
From Abbyy
Where
NOT EXISTS
(Select EngineNo, VehRegNo
From eDaftarOwnerDetail
Where eDaftarOwnerDetail.EngineNo = @Identity
and eDaftarOwnerDetail.VehRegNo = @RegNo))
UPDATE Abbyy
SET CouponStatus = 'Reject', RejectCode = 'A3'
WHERE EngineNo = @Identity
and VehRegNo = @RegNo
and DocID=@PriKey

Else If EXISTS (Select COUNT(1)
From Abbyy
Where EngineNo = @Identity
Group by EngineNo
Having COUNT(1)>2)
UPDATE Abbyy
SET CouponStatus = 'Reject', RejectCode = 'A4'
WHERE EngineNo = @Identity
and DocID=@PriKey
and CouponStatus=@CouStatus

Else If EXISTS (Select ProcessingDate, DateOfService
From Abbyy
Where
datediff(day, @SerDate, @ProDate)>210)
UPDATE Abbyy
SET CouponStatus = 'Reject', RejectCode = 'A5'
WHERE CONVERT(DATE,ProcessingDate,102) = CONVERT(DATE,@ProDate,102)
and CONVERT(DATE,DateOfService,102) = CONVERT(DATE,@SerDate,102)
and DocID=@PriKey

Else
UPDATE Abbyy
Set CouponStatus = 'Approve', RejectCode = ''
WHERE EngineNo = @Identity

END