Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Question Unanswered: INSTEAD OF INSERT trigger with SQLServer

    I want to insert a record with 2 joined tables. I'm trying to use a trigger, when a value '0' in the 2 fields before insert the record, I want to replace them by 'NULL' because this fields are in a foreign key. I have not examples to do it.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a function to do the conversion, and then put the function in your trigger.

    Create Function Zero2Null(@Value Decimal(38, 5))
    Returns Decimal(38, 5)
    as
    begin
    declare @ReturnValue Float
    if @Value <> 0 set @ReturnValue = @Value
    return @ReturnValue
    end
    go

    declare @TestValue int
    set @testValue = 5
    select dbo.Zero2Null(@TestValue)
    set @TestValue = 0
    select dbo.Zero2Null(@TestValue)

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    Code:
    create trigger...blah-blah-blah...
       insert other_table (f1, f2, f3)
          select nullif(f1, 0), nullif(f2, 0), f3 from inserted

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, Nullif is better.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •