Tuesday, May 7, 2013

T-SQL Instead of Insert, replace column values


create trigger SetNegativeBalanceToZero
on dbo.CoopBucksBalances

instead of insert

as

    if exists
    (
        select *
        from inserted
        where StartBalance < 0
    )
    begin
 INSERT INTO dbo.CoopBucksBalances (
        Branch,
        StartBalance,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime

    ) SELECT
        Branch,
        0,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime
     FROM
        INSERTED      
    end
    else
    begin
     INSERT INTO dbo.CoopBucksBalances (
        Branch,
        StartBalance,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime

    ) SELECT
        Branch,
        StartBalance,
PrevYearStartBalance,
CurrentDayUsage,
ImportDateTime
     FROM
        INSERTED
    end

go