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'))),

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