1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

update table fieds based on trigger inserted

Discussion in 'SQL Server' started by mapsv3, Dec 7, 2012.

  1. mapsv3

    mapsv3 New Member

    Joined:
    Dec 6, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  2. mapsv3

    mapsv3 New Member

    Joined:
    Dec 6, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    [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')**
     
  3. mapsv3

    mapsv3 New Member

    Joined:
    Dec 6, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page