CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
Texie
Texie

Reputation: 17

Trigger Insert ONLY if column value contains XXXX

Let's say we have a table where there are many Unique codes combined. For example: Bill No:

I wanted to create a trigger that ensures only the ones with value 'XOUT%' get used in the trigger and inserted into another table. This specifically seemed to work (I think) however, then when I create a document where format B is used, I get an error saving the document.

The tables are as follows:

  1. 1 table called ICSTOCKBILL where all deliveries coming in and out are saved into that one table using the formats as described above.
  2. One column = FBillNo where the bill numbers are registered to.
  3. One column = FStatus, where the approval status is updated to 1 if so.

1 Table called 3026 where the stock bill numbers from ICSTOCKBILL (FBillNo) should automatically be transferred to.

My code is as follows:

create Trigger DVLP_T_InsertBillNoItemSALESDEL2 On ICStockBill
for UpDate
as
If UpDate(FStatus)  
Begin
    If not exists (Select 1 
                  From inserted a 
                  Inner Join t_Item b on b.FItemClassID=3026 AND b.FNumber=a.FBillNo where FBillNo like 'XOUT%')
    Begin
        INSERT INTO t_Item (FItemClassID,FParentID,FLevel,FName,FNumber,FShortNumber,FFullNumber,FFullName,FDetail,FDeleted) 
        Select 3026,0,1,FBillNo,FBillNo,FBillNo,FBillNo,FBillNo,1,0
        From inserted where FBillNo like 'XOUT%'

    End
End

So the end goal is: All documents that are approved (FStatus = updated) must run through the trigger. But ONLY those with XOUT format should be inserted into Table 3026.

So far, after I apply this trigger, it works. I create a new sales delivery, it's not in the new table yet. I approve it, and it's there.

However, other documents that do not have said format, get the error:

UPDATE ICSTOCKBILL SET FORDERAFFIRM=0 WHERE FBILLNO=SEOUT1234 violiation of UNIQUE KEY contraint 'item2;.

Any tips would be appreciated.

Upvotes: 0

Views: 141

Answers (1)

Dale K
Dale K

Reputation: 27202

You can simplify the entire trigger by using proper set based logic - instead of procedural logic.

The following should accomplish what you are asking for.

create trigger DVLP_T_InsertBillNoItemSALESDEL2 on ICStockBill
for update
as
begin
    set nocount on;

    -- Always use meaningful aliases - using a, b, c is asking for trouble
    insert into t_Item (FItemClassID, FParentID, FLevel, FName, FNumber, FShortNumber, FFullNumber, FFullName, FDetail, FDeleted) 
    select 3026, 0, 1, FBillNo, FBillNo, FBillNo, FBillNo, FBillNo, 1, 0
    from Inserted I
    where FBillNo like 'XOUT%'
    and FStatus = 1
    and not exists (
        select 1
        from t_Item IT
        where IT.FItemClassID = 3026
        and IT.FNumber = I.FBillNo
    );
end

If you are still getting the error originally reported then I very much doubt it has anything to do with the trigger. Its reporting that a unique constraint on the main table has been violated, and the trigger doesn't touch that table. I suspect your test data might not be unique enough.

Upvotes: 2

Related Questions