Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: create trigger not insert if

    how can i Create a trigger to check if a value is NULL or = 0

    i am trying :

    CREATE TRIGGER [TR_User]
    ON [User]
    AFTER INSERT
    AS
    BEGIN
    SET NOCOUNT ON;

    DELETE FROM User WHERE (Category = 0 OR Category IS NULL)
    END


    but in that way i dont CHECK the new inserted value directly, is it possible not to INSERT it if the value is NULL or = 0 ?

    not to look all the table for each new line inserted

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Why not just create a constraint:

    Category > 0
    (assuming it is positive only)

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    you mean I must create a contrainst on the column ?

    how can i do it

    CREATE CONTRAINST check_CAT ..... Category > 0 ??
    thank you

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    you mean I must create a contrainst on the column ?

    how can i do it

    CREATE CONTRAINST check_CAT ..... Category > 0 ??
    thank you
    I would suggest you to try BOL for CHECK...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    I dont know how to use it ? and it is not a boolean but an integer ?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You don't know how to use BoL? You will really struggle if you don't learn to....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    I dont know how to use it ? and it is not a boolean but an integer ?
    BOL -Books Online
    Start from here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    yes i have done the trigger
    CREATE TRIGGER [TR_User]
    ON [User]
    AFTER INSERT
    AS
    BEGIN
    SET NOCOUNT ON;

    DELETE FROM User WHERE (Category = 0 OR Category IS NULL)
    END

    but i think there is a best way not to accept <=0

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sure there is a better way.

    USE A CONSTRAINT.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Dec 2005
    Posts
    266
    if i use a contrainst it will crash the application

    i have not created the database and another application is using it
    i must clean the bugs of this application without disturbing it .. thats the job

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    i must clean the bugs of this application without disturbing it .. thats the job
    Well - that will be tricky.
    Do you mean you have been tasked with sorting out an application without touching the application code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    By using a trigger you are allowing the interface to submit bad data, and then deleting the data without informing the interface of the error.

    That is a bug.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Dec 2005
    Posts
    266
    i cannot change the other application

    but mine needs clean datas , and with this trigger they will be clean

    thank you

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Does the application access the tables directly (i.e. not via stored procedures or views)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by anselme
    i cannot change the other application

    but mine needs clean datas , and with this trigger they will be clean

    thank you
    Whoa ... hold on cowboy. Are you saying that the data needs to be inspected coming into your application. If so then establish a check in your select statement and use a CASE to replace unacceptable values (NULL or = 0 ) with the value of your choice.

    That way you avoid messing with the tabled data, and have valid values returned to you.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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