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