locked
Best way to write Sql Trigger RRS feed

  • Question

  • User1126057398 posted

    I had to write a SQL Trigger in which I had to decrease Approverno if any approver in between had been deleted. eg for an incentive say I1 if there are 4 approvers, and let's say 2nd approver is deleted, then, 3rd approver no. will be 2 and 4th approver no. will be 2. For that I had created Trigger like below. But just wondering can I straightaway write a single update query to do the same. Performance wise which will be better. Please do suggest.

    CREATE Trigger trg_trInc_Update
    ON tbInc
    After Update
    As
    BEGIN
    SET NOCOUNT ON;
    Declare @userId int;Declare @fieldNm varchar(25);Declare @cntApprover int;Declare @IncentiveId int;Declare @ApproverNo int;
    SELECT @userId = inserted.UserId,@IncentiveId=Inserted.IncentiveId,@ApproverNo=ApproverNo FROM inserted
    Select @fieldNm=FieldNm from FieldValue where FieldValueId=(Select FieldValueId from tbInc where UserId = @userId)
    IF (@fieldNm='Approver')
    BEGIN
    Select @cntApprover=count(UserId) from tbInc where IncentiveId=@IncentiveId and ApproverNo>@ApproverNo and IsDeleted=0
    if(@cntApprover>0)
    BEGIN
    Update tbInc Set ApproverNo=ApproverNo-1 where IncentiveId=@IncentiveId and ApproverNo>@ApproverNo and IsDeleted=0
    END
    END

    END

    Sunday, November 11, 2018 8:23 AM

All replies

  • User364663285 posted

    Table trigger can be your way to achieve what you need. But you can also use other way, like to have one pre-check time, to each time "count" all "existing" Approver number, instead, before doing the relevant update.

    Sunday, November 11, 2018 1:30 PM
  • User1126057398 posted

    Thanks for replying. Please do elaborate with an example.

    Sunday, November 11, 2018 4:20 PM
  • User364663285 posted

    Do a pre-check like

    Select @cntApprover=count(UserId) from tbInc where IncentiveId=@IncentiveId and ApproverNo>@ApproverNo and IsDeleted=0 

    to get number of existing records, every time before doing insert to the table.

    Monday, November 12, 2018 5:21 AM
  • User77042963 posted

    When you use variables in your trigger, you will have a problem. When you have multiple rows operation (it should), you code will work for only one row and it is not right.  Try to think about your issue again to see whether you can solve your problem without a trigger. If you do want to use trigger, use join with inserted/deleted tables to process, get rid of these variables.

    Monday, November 12, 2018 2:53 PM