0

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.

Improve this question
1
  • 3
    It's not at all clear how to fix this broken trigger since it's using several variables that are never initialized to any values. Generally though, you seek to avoid scalar variables and just write an INSERT ... SELECT ... where inserted is just one of the tables in the FROM clause. Commented Aug 15, 2016 at 9:33

3 Answers 3

Reset to default
2

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

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.

Instead use Inserted and Deleted internal tables.

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

Comments

1

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

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
Improve this answer

7 Comments

Thank you, now my trigger can run (y). But, there are still something missing k.uh_nominal did not inserted to the trs_uangharian, the trigger insert NULL value. Can we make the SET '@thn_harian' and SET @date_diff from inserted i to? I think if we make it from inserted it will run perfectly. Do you know how to make it, if y please tell me
As I wrote previously, all trigger is messed up. I don't get where you take @thn_harian from? Same column from inserted (master_st)? Same question about @tgl_selesai, @tgl_mulai?
This is the actual problem. stackoverflow.com/questions/38948611/using-function-in-trigger/… Anyway thanks for helping @gofr1
What for you need @date_diff and @thn_harian? They can not be inserted into [dbo].[trs_uangharian]. What is your logic beside this dates and date differences?
i have data from other table about jobtask that can be paid with 3 scenario. (paid in beggining, paid 20-80, paid in end) I make an app to calculate the cost for the timeline. A jobtask contain datestart & dateend and will be done by multiple person. i import the data from that database to my database. when a user choose a jobtask (in a gridview) all the detail imported to my master_st table I make table for every cost (hotelcost, transportcost, dailycost) the cost is depend on location and year of the jobtask be done. date_diff is to count the total paid (cost per day*date_diff)
|
1

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

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.

Improve this answer

4 Comments

Thank you for your solution, but '@uangharian100' did not directly provided by the initial table. I must querying the value of '@uangharian100' (cost) from the reference table which is contain '@tgl_mulai (datestarted)' and '@tgl_selesai(dateend)' value.
The three SET statments that calculate @uangharian100 in your code are not showing any dependency to the value retreived from the INSERTED table. Thus it would appear that the same value can be used for every row. If this isn't the case then I'm not sure how to help you. Do you have a colegue that knows SQL that you could talk the issue through with?
@MartinBrown exactly! to set @uangharian100 OP uses [dbo].[master_st] table, the one that trigger is on. So he can simply join inserted with table he need to get data from. Like in my answer.
This is the actual problem. Unfortunately i dont have anyone to ask :D. Thats why i post in stockoverflow. I'm a newbie in C# and SQL Server programming. Thanks all. stackoverflow.com/questions/38948611/using-function-in-trigger/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.