Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have stuck on this trigger when I use to select case from the inserted table, the result was NULL.

 TRIGGER trgInsertPenjDetail ON [dbo].[TbDtlOutBrgGd1] 
 AFTER INSERT
BEGIN
    DECLARE @namaProduct varchar (255)
    DECLARE @jenisProduct varchar (50)
    SET @jenisProduct = (select jenis from Inserted) 
    SELECT @namaProduct =
        CASE @jenisProduct
                    WHEN 'PAKET'
                               THEN (SELECT tb.nm AS namaProduct from dbo.TbHdPaket AS tb
                INNER JOIN Inserted AS i ON tb.id = i.brg)
                    WHEN 'TERAPI'
                               THEN (SELECT tb.nm AS namaProduct from dbo.TbMterapi AS tb
                INNER JOIN Inserted AS i ON tb.id = i.brg)
            WHEN 'BARANG'
                               THEN (SELECT tb.nama AS namaProduct from dbo.TbMstBb AS tb
                INNER JOIN Inserted AS i ON tb.id = i.brg)
                    ELSE '-'
    BEGIN
        UPDATE b
        SET b.rek = b.rek + '( ' + convert(varchar(5),i.qty) + ' ' + @namaProduct+' ' + i.ket+ ' )'
        FROM dbo.TbRek AS b
        INNER JOIN Inserted AS i ON b.nott = i.nott
    BEGIN
        UPDATE b
        SET    b.rek = replace(b.rek, ')(', '+')
        FROM dbo.TbRek AS b
        INNER JOIN Inserted AS i ON b.nott = i.nott
What is the right syntax for CASE or IF on this trigger? Thanks.
                SET @jenisProduct = (select jenis from Inserted) is broken - INSERTs can insert multiple rows. The trigger is called once for that entire batch. There's a reason that inserted resembles a table - you should treat it as one.
– Damien_The_Unbeliever
                Mar 13, 2012 at 8:53
I would change the top part of the trigger so that the trigger looks like this:
CREATE TRIGGER trgInsertPenjDetail ON [dbo].[TbDtlOutBrgGd1]  
 AFTER INSERT 
BEGIN 
    BEGIN 
        UPDATE b 
        SET b.rek = b.rek + '( ' + convert(varchar(5),i.qty) + ' ' + 
        CASE i.jenis 
            WHEN 'PAKET'  THEN nama1
            WHEN 'TERAPI' then name2
            WHEN 'BARANG' THEN nama3
            ELSE '-' 
        END + ' ' + i.ket+ ' )' 
        FROM dbo.TbRek AS b 
        INNER JOIN (Select qty, jenis, ket, t1.nm as nama1, t2.nm as nama2, t3.nama as nama3 from Inserted as i
                        LEFT JOIN TbHdPaket as t1 on i.brg = t1.id
                        LEFT JOIN TbMterapi as t2 on i.brg = t2.id
                        LEFT JOIN TbMstBb as t3 on i.brg = t3.id) AS i ON b.nott = i.nott 
    BEGIN 
        UPDATE b 
        SET    b.rek = replace(b.rek, ')(', '+') 
        FROM dbo.TbRek AS b 
        INNER JOIN Inserted AS i ON b.nott = i.nott 
This way even with multiple inserts the inserted table should update TbRek with everything that gets updated.
I assume the bottom part of the query is just to make sure that ")(" gets replaced with a "+".
                Your point about multiple inserts is almost right - however, the behaviour of that UPDATE if multiple inserted rows have the same nott value is not pretty to contemplate. It may perform one update to the target row. It's unlikely to preform all of the updates the OP expects.
– Damien_The_Unbeliever
                Mar 13, 2012 at 9:49
This code works after I modify @Jaques's answered using if else. Thanks for taking time to answer.
CREATE  TRIGGER trgInsertPenjDetail ON [dbo].[TbDtlOutBrgGd1]  
AFTER INSERT 
BEGIN 
    DECLARE @namaProduct varchar (255)
    DECLARE @jenis varchar (50)
    SELECT @jenis = (select jenis from inserted)
    IF @jenis = 'PAKET'
        SELECT @namaProduct = (SELECT tp.nm AS namaProduct from dbo.TbHdPaket AS tp
                INNER JOIN Inserted AS i ON tp.id = i.brg)
    ELSE IF @jenis = 'TERAPI'
        SELECT  @namaProduct = (SELECT tt.nm AS namaProduct from dbo.TbMterapi AS tt
                INNER JOIN Inserted AS i ON tt.id = i.brg)
    ELSE SELECT  @namaProduct = (SELECT tb.nama AS namaProduct from dbo.TbMstBb AS tb
                INNER JOIN Inserted AS i ON tb.id = i.brg)
    BEGIN 
        UPDATE b 
        SET b.rek = b.rek + '( ' + convert(varchar(5),i.qty) + ' ' + @namaProduct + ' ' + i.ket+ ' )' 
        FROM dbo.TbRek AS b 
        INNER JOIN Inserted AS i ON b.nott = i.nott
    BEGIN 
        UPDATE b 
        SET    b.rek = replace(b.rek, ')(', '+') 
        FROM dbo.TbRek AS b 
        INNER JOIN Inserted AS i ON b.nott = i.nott 
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.