SQL Server触发多行

I have a trigger:

我有一个触发器：

ALTER TRIGGER [dbo].[tg_trs_uharian] ON [dbo].[master_st] 
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON

    declare @tgl_mulai varchar(10),
            @tgl_selesai varchar(10),
            @kdlokasi int,
            @thn_harian int,
            @date_diff int

    declare @tugasID int;
    declare @uangharian20 decimal(15,2);
    declare @uangharian80 decimal(15,2);
    declare @uangharian100 decimal(15,2);

    select @tugasID=tugasID 
    from inserted

    SET @thn_harian=CAST(YEAR(CONVERT(datetime, @tgl_mulai, 103)) AS INT);

    SET @date_diff=((SELECT datediff(day,CONVERT([datetime],@tgl_mulai,(103)),CONVERT([datetime],@tgl_selesai,(103))))+1);

    SET @uangharian100 = (
                        SELECT k.uh_nominal 
                        FROM master_st m 
                        LEFT OUTER JOIN ref_uharian AS k 
                            ON k.uh_kdlokasi=m.kdlokasi AND k.uh_tahun=@thn_harian);

    insert into trs_uangharian (tugasID, uangharian100) values 
    (@tugasID, @uangharian100);
END

How to make select @tugasID=tugasID from inserted applicable for multiple row inserted row table with different tugasID? It seems that my code is applicable for single row only.

如何从插入中选择@ tugasID = tugasID适用于具有不同tugasID的多行插入行表？似乎我的代码仅适用于单行。

3 个解决方案

#1

It seems that @date_diff is not used You use @thn_harian so we need @tgl_mulai, but it is NULL by default So your INSERT statement has some problems. I assumed that @tgl_mulai is a column of the original table master_st so I treat it as a column of "inserted" trigger internal table

似乎没有使用@date_diff你使用@thn_harian所以我们需要@tgl_mulai，但默认情况下它是NULL所以你的INSERT语句有一些问题。我假设@tgl_mulai是原始表master_st的一列，所以我把它当作“插入”触发器内部表的一列

ALTER TRIGGER [dbo].[tg_trs_uharian] ON [dbo].[master_st] 
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON

    insert into trs_uangharian(tugasID, uangharian100)
    select 
        i.tugasID,
        k.uh_nominal 
    from inserted i
    left join ref_uharian AS k 
                ON k.uh_kdlokasi = i.kdlokasi AND 
                   k.uh_tahun = CAST(YEAR(CONVERT(datetime, i.tgl_mulai, 103)) AS INT)
END

Please, this is a common problem among new SQL developers SQL triggers work set-based. Do not calculate any value using variables. These can only store the last row's calculations in general.

请问，这是新SQL开发人员SQL触发器基于工作集的常见问题。不要使用变量计算任何值。这些只能存储最后一行的计算。

Instead use Inserted and Deleted internal tables.

而是使用Inserted和Deleted内部表。

#2

Your query is messed up a bit, so I can provide only general solution. Change INSERT part on something like this:

您的查询有点乱，所以我只能提供一般解决方案。在这样的事情上更改INSERT部分：

INSERT INTO trs_uangharian (tugasID, uangharian100) 
SELECT  i.tugasID,
        k.uh_nominal
FROM inserted i
LEFT JOIN ref_uharian AS k 
    ON k.uh_kdlokasi=i.kdlokasi AND k.uh_tahun=@thn_harian

#3

You should be able to replace the INSERT statement with this:

您应该能够用以下内容替换INSERT语句：

INSERT INTO trs_uangharian (tugasID, uangharian100) 
SELECT
    tugasID,
    @uangharian100
FROM
    inserted

However it looks like you also have an issue with @tgl_mulai and @tgl_selesai not being set to anything.

但是看起来你也遇到了@tgl_mulai和@tgl_selesai没有被设置为任何问题的问题。

#1

ALTER TRIGGER [dbo].[tg_trs_uharian] ON [dbo].[master_st] 
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON

    insert into trs_uangharian(tugasID, uangharian100)
    select 
        i.tugasID,
        k.uh_nominal 
    from inserted i
    left join ref_uharian AS k 
                ON k.uh_kdlokasi = i.kdlokasi AND 
                   k.uh_tahun = CAST(YEAR(CONVERT(datetime, i.tgl_mulai, 103)) AS INT)
END