Results 1 to 15 of 15

Thread: trigger help

  1. #1
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    Unanswered: trigger help

    MSSQL 2000
    I am comparing a columns "deleted" value to the "inserted" value in a trigger. Everything works fine unless there is a null value in one of the columns. If there is a null in either field the comparison evalutes to equal even when there is a value in the opposing field. Is this a T-SQL behavior I am not familiar with?
    Last edited by jbuhl; 05-18-06 at 17:55.

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Have U tried looking at

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO

    ?

    When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN.
    BOL.

    Hope it helps

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28
    Thank you GW

    That's eactly what I needed.

    Joe

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can use some built-in TSQL functions to accomplish the same thing without changing system settings. Post the code for your trigger.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    trigger help

    As I expected issueing the "set" command inside a trigger did not appear to have any effect.

    Here is the code.


    CREATE TRIGGER [custom_iv00101_upd] ON [dbo].[custom_IV00101]
    FOR UPDATE

    AS
    /********************************************
    PR:2943
    Date:05/06
    Auth:jbuhl
    Desc: Audit the modifications to the
    table.

    ********************************************/

    DECLARE @operation char(1),
    @table_name varchar(50),
    @itemnmbr char(31),
    @loginname varchar(30),
    @cname varchar(50),
    @ovalue varchar(250),
    @nvalue varchar(250)


    set @operation ='U'
    set @table_name ='IV00101'
    select @itemnmbr =itemnmbr from inserted
    select @loginname =loginame
    from master..sysprocesses
    where spid=@@spid

    select @ovalue=parent_itemnmbr from deleted
    select @nvalue=parent_itemnmbr from inserted
    if @ovalue <> @nvalue
    begin
    set @cname='parent_itemnmbr'
    exec gp_activity_ins @table_name ,@cname
    ,@itemnmbr ,@operation
    ,@ovalue ,@nvalue
    ,@loginname
    end

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Thumbs up

    You're Welcome jbuhl

    Glad 2 Help :-)

    Don't forget

    Keep an eye on setting ANSI_NULLS etc. back to Default when U finished with em
    PLUS
    SQL has a habit of saving the ANSI settings at funny times.

    I 2 would love 2 C blindmans solution to this little piggy

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    trigger help

    Yeah, as of now I am just using "isnull" function to set the string to 'UNKOWN'. I don't think that value will ever end up in any of these fields.

    This is a COTS application and I don't dare want to mess with any server settings. We are already viewed as intruders by creating our own objects. joe

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Cool

    That's what BOL say's it is so - why not if you can get away with it.

    sometimes u can't.

    GW

    PS. I may be wrong bbut I think your trigger will blow up as soon as you do more than a singleton operation on the table - lol

    ie. select @ovalue=parent_itemnmbr from deleted
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    trigger help

    Let her blow.

    I have been promised that the application only updates one row at a time.


    I am generally a Sybase DBA and don't do much MSSQL and I code for multi row action by using cursors. In Oracle I seem to remember they have a "For all Rows" clause.

    Does MSSQL have such an option or is the solution resovled with a cursor on the affect rows?

    Joe

  10. #10
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Exclamation

    LMFAO

    Quote Originally Posted by jbuhl
    Let her blow.

    I have been promised that the application only updates one row at a time.


    I am generally a Sybase DBA and don't do much MSSQL and I code for multi row action by using cursors. In Oracle I seem to remember they have a "For all Rows" clause.

    Does MSSQL have such an option or is the solution resovled with a cursor on the affect rows?

    Joe

    Ref "..and I code for multi row action by using cursors...."

    And then I shuddered

    Good Luck M8y

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jbuhl
    Let her blow.

    I have been promised that the application only updates one row at a time.
    You have that in writing, I assume? I would NEVER allow a trigger such as this in any database. It is misuse of triggers like this which has resulted in their bad reputation.

    Quote Originally Posted by jbuhl
    I am generally a Sybase DBA and don't do much MSSQL and I code for multi row action by using cursors. In Oracle I seem to remember they have a "For all Rows" clause.
    Neither Sybase or MSSQL encourages the use of cursors. You should not be using them in MSSQL, and you should not be using them in Sybase either. It is Oracle that is cursor-intensive.

    As far as your trigger logic and the evaluation of NULLs is concerned, try this code:
    Code:
    declare	@ovalue varchar(10)
    declare	@nvalue varchar(10)
    
    set	@ovalue = 'test'
    set	@nvalue = null
    
    --this logic fails, because NULL's are "undefined", and thus never "equal to" or "not equal to" any defined value.
    if @ovalue <> @nvalue
    begin
    	print 'do something'
    end
    else
    begin
    	print 'do nothing'
    end
    
    --this logic succeeds, because the sproc you want to run is included in the "else" portion of the statement.
    if @ovalue = @nvalue
    begin
    	print 'do nothing'
    end
    else
    begin
    	print 'do something'
    end
    
    --this logic succeeds by converting NULLs to a neutral value (zero-length string).
    if isnull(@ovalue, '') <> isnull(@nvalue, '')
    begin
    	print 'do something'
    end
    else
    begin
    	print 'do nothing'
    end
    But if you don't drop that stored procedure call in your trigger and write something that can handle the multi-record operations that WILL occur, be prepared for a major disaster.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but note that the second method I give above will 'do something' if both values are NULL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28
    Quote Originally Posted by blindman

    ([sermon snip)

    Neither Sybase or MSSQL encourages the use of cursors. You should not be using them in MSSQL, and you should not be using them in Sybase either. It is Oracle that is cursor-intensive.
    .

    How do you evaluate each row in an affected result set?

  14. #14
    Join Date
    Dec 2005
    Posts
    1
    learning...

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jbuhl
    How do you evaluate each row in an affected result set?
    I don't. I use set-based processing. If I wanted to work on records one at a time I'd be a .Net developer. Or maybe do more Oracle work...

    Just for fun, post the code for [gp_activity_ins]. I suspect that is at the root of all your problems.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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