Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    23

    Unanswered: question about delete statement

    Hi,My question is :
    CREATE TABLE t1
    (c1 INTEGER,
    c2 INTEGER,
    c3 DECIMAL(15,0 ))
    INSERT INTO t1 VALUES (1, 2, 3.0)
    How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
    Thanks !

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you give more details on what you are trying to do? I suspect that there are better ways to accomplish whatever you need to do.

    To answer your question as it was posted, you could create a trigger on the T2 table that updated the T1 table appropriately. I'm a little vague on the implementation details because it seems that you are looking for more than a simple counter, and you haven't provided a lot of details on what you want to happen.

    -PatP

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    CREATE TRIGGER myTriggerForDelete
    ON T2
    AFTER DELETE
    AS
    UPDATE T1
    SET C1=COALESCE((SELECT MIN(C1) FROM T1),0)-1
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    It should be something like my previous post.

    But as Pat said, I don't think this makes senses, maybe you should give us more explanations.

    Hope it can helps you
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    please go to books online and read the article on CREATE TRIGGER and pay special attention to the section on the inserted and deleted tables.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you trying to prevent "holes" in a meaningless "key"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Brett Kaiser
    Are you trying to prevent "holes" in a meaningless "key"
    If it's the case you should read that:
    http://guelphdad.wefixtech.co.uk/sqlhelp/gaps.shtml
    Less is more.
    How long is now?
    http://www.lesouterrain.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
  •