update table fieds based on trigger inserted

mapsv3's Avatar, Join Date: Dec 2012
Newbie Member
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

Code:
[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')**
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

Code:
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
0
mapsv3's Avatar, Join Date: Dec 2012
Newbie Member
[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')**
0
mapsv3's Avatar, Join Date: Dec 2012
Newbie Member
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