How to create multirow after insert trigger sql

Question

I created a trigger for single row insert but it does not work on multi row insert.

Here is my table:

create table tb_pengiriman
(
    id_pengiriman char(5) not null primary key
        constraint cek_id_pengiriman check (id_pengiriman like('PE[0-9][0-9][0-9]')),
    provinsi varchar(30) not null,
    kota varchar(30) not null,
    harga_ongkir bigint null
        constraint cek_harga_ongkir check (harga_ongkir >-1)
);

alter table tb_pengiriman add
    constraint cek_kota_banten 
    check ((provinsi = 'Banten') and (kota in ('Tangerang','Serang','Cilegon','Tangerang Selatan'))),

and here is the trigger:

create trigger tr_pengiriman
on tb_pengiriman
after insert
as
    begin

        declare @id_pengiriman char(5), 
                @provinsi varchar(30), 
                @kota varchar(30);

        select @id_pengiriman = id_pengiriman from inserted;
        select @provinsi = provinsi from inserted;
        select @kota = kota from inserted;
        exec sp_pengiriman @id_pengiriman,@provinsi,@kota;

    end
    go

    create procedure sp_pengiriman
    @id_pengiriman char(5), 
    @provinsi varchar(30), 
    @kota varchar(30)
    As
    Begin
        --proses kalkulasi ongkos kirim
        if(@kota = 'Tangerang')
        begin
            update tb_pengiriman 
            set harga_ongkir = 20000 
            where id_pengiriman = @id_pengiriman
        end
        else if(@kota = 'Serang')
        begin
            update tb_pengiriman 
            set harga_ongkir = 50000 
            where id_pengiriman = @id_pengiriman
        end
        else if(@kota = 'Tangerang Selatan')
        begin
            update tb_pengiriman 
            set harga_ongkir = 15000 
            where id_pengiriman = @id_pengiriman
        end
        else if(@kota = 'Cilegon')
        begin
            update tb_pengiriman 
            set harga_ongkir = 30000 
            where id_pengiriman = @id_pengiriman
        end
        else 
        begin
            rollback transaction
            print 'kota tidak terdaftar'
        end
    end
    go

This code works for a single row insert but when I try to do a multi row insert all of the rows change to the same trigger-procedure update query.

How do I make this query work for multi row insert as well as single row insert?

Several things are very bad here. First is your procedure that is rolling back a transaction it didn't start. There are several layers of bad in that alone. You should never do a rollback or commit in a trigger. If your calling code is using a transaction chances are you will break it. You also have the sp_ prefix on your procedure. This is not a good idea. sqlperformance.com/2012/10/t-sql-queries/sp_prefix But really you don't even need that procedure. It could be a single update statement. And why are you trying to update the row you just inserted? — Sean Lange
– Sean Lange, Commented Mar 22, 2018 at 18:04

Sean Lange · Accepted Answer · 2018-03-22 18:22:02Z

1

You could simplify this about 1000% by just using a computed column. There is no need for a trigger at all here. Your constraint forces the value of kota to be only one of those four values. This should accomplish what it appears you are trying to do without any need for triggers and such.

create table tb_pengiriman
(
    id_pengiriman char(5) not null primary key
        constraint cek_id_pengiriman check (id_pengiriman like('PE[0-9][0-9][0-9]')),
    provinsi varchar(30) not null,
    kota varchar(30) not null,
    harga_ongkir as case kota when 'Tangerang' then 20000 
                        when 'Serang' then 50000
                        when 'Tangerang Selatan' then 15000
                        when 'Cilegon' then 30000
                        end persisted
        constraint cek_harga_ongkir check (harga_ongkir >-1)
);

answered Mar 22, 2018 at 18:22

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create multirow after insert trigger sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related