Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   update table fieds based on trigger inserted (http://www.go4expert.com/forums/update-table-fieds-based-trigger-t29326/)

mapsv3 7Dec2012 12:06

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

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

mapsv3 7Dec2012 12:07

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')**

mapsv3 7Dec2012 12:07

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


All times are GMT +5.5. The time now is 00:48.