Professional Web Applications Themes

Trigger Help Needed. - Microsoft SQL / MS SQL Server

I hope somebody will help me with this problem. I have a table in which one of the columns (KeyID) is used to keep order of the records for displaying purposes: CREATE TABLE [dbo].[zz_jrTest1] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [KeyID] [int] NULL , [KeyName] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (0, 'X0') INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (1, 'A1') INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (3, 'C3') INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (4, 'D4') INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (5, 'E5') I like to ...

  1. #1

    Default Trigger Help Needed.

    I hope somebody will help me with this problem.
    I have a table in which one of the columns (KeyID)
    is used to keep order of the records for displaying
    purposes:

    CREATE TABLE [dbo].[zz_jrTest1] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [KeyID] [int] NULL ,
    [KeyName] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (0, 'X0')
    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (1, 'A1')
    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (3, 'C3')
    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (4, 'D4')
    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (5, 'E5')

    I like to created trigger(s) which will keep the sequence of records,
    no matter if a new record is added, deleted, or updated. There is
    going to be just one record inserted, deleted or updated at any
    given time. Records with KeyID = 0 (zero) should be excluded,
    this means that this table can have more than 1 record with
    KeyID = 0.

    Let say the data in the zz_jrTest1 table looks like this:

    ID KeyID KeyName
    ------- ---------------- ----------------
    1 0 X0
    2 1 A1
    3 3 C3
    4 4 D4
    5 5 E5

    when this statement is run:
    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (4, 'X4')

    The table should look like this:

    ID KeyID KeyName
    ------- ---------------- ----------------
    1 0 X0
    2 1 A1
    3 3 C3
    6 4 X4
    4 5 D4
    5 6 E5

    when this statement is run:
    INSERT INTO zz_jrTest1 (KeyID, KeyName) VALUES (2, 'B2')

    The table should look like this:

    ID KeyID KeyName
    ------- ---------------- ----------------
    1 0 X0
    2 1 A1
    7 2 B2
    3 3 C3
    6 4 X4
    4 5 D4
    5 6 E5

    when below DELETE statement is run the table should look like this:
    DELETE zz_jrTest1 WHERE KeyID = 4

    ID KeyID KeyName
    ------- ---------------- ----------------
    1 0 X0
    2 1 A1
    7 2 B2
    3 3 C3
    6 4 X4

    5 5 E5

    when below UPDATE statement is run the table should look like this:
    UPDATE zz_jrTest1 SET KeyID = 2 WHERE KeyID = 4

    ID KeyID KeyName
    ------- ---------------- ----------------
    1 0 X0
    2 1 A1
    6 2 X4
    7 3 B2
    3 4 C3

    5 5 E5


    when below UPDATE statement is run the table should look like this:
    UPDATE zz_jrTest1 SET KeyID = 5 WHERE KeyID = 1

    ID KeyID KeyName
    ------- ---------------- ----------------
    1 0 X0

    6 1 X4
    7 2 B2
    3 3 C3
    5 4 E5
    2 5 A1

    I hope all this illustrates the problem I have to solve.

    Thanks a lot,
    J. Rychter




    jr Guest

  2. #2

    Default Re: Trigger Help Needed.

    Why do you care about how it is stored? In your queries (to return rows)
    why don't you just use an "order by" statement?

    If you really want that functionality you can put a non-unique clustered
    index on the keyid.

    Ray Higdon MCSE, MCDBA, CCNA

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ray Guest

  3. #3

    Default Re: Trigger Help Needed.

    Insert:
    CREATE TRIGGER trgi_i_zz_jrTest1 ON zz_jrTest1 INSTEAD OF INSERT
    AS
    IF ROWCOUNT = 0 OR TRIGGER_NESTLEVEL() >1 RETURN
    IF SELECT COUNT(*) FROM inserted > 1
    BEGIN
    RAISERROR ('Can only insert 1 row at a time', 16,1)
    ROLLBACK TRANSACTION
    RETURN
    END

    IF EXISTS (SELECT * FROM zz_jrTest1 z INNER JOIN inserted i
    ON z.KeyID = i.KeyID)
    UPDATE zz_jrTest1
    SET KeyID = KeyID + 1
    WHERE KeyID >= (SELECT KeyID FROM inserted)

    INSERT INTO zz_jrTest1 (KeyID, KeyName)
    SELECT KeyID, KeyName FROM inserted

    Delete:
    CREATE TRIGGER trga_d_zz_jrTest1 ON zz_jrTest1 AFTER DELETE
    AS
    IF ROWCOUNT = 0 OR TRIGGER_NESTLEVEL() >1 RETURN
    IF SELECT COUNT(*) FROM deleted > 1
    BEGIN
    RAISERROR ('Can only insert 1 row at a time', 16,1)
    ROLLBACK TRANSACTION
    RETURN
    END
    UPDATE zz_jrTest1
    SET KeyID = KeyID - 1
    WHERE KeyID > (SELECT KeyID FROM deleted)

    Update:
    CREATE TRIGGER trgi_u_zz_jrTest1 ON zz_jrTest1 INSTEAD OF UPDATE
    AS
    IF ROWCOUNT = 0 OR TRIGGER_NESTLEVEL() >1 RETURN
    IF SELECT COUNT(*) FROM inserted > 1
    BEGIN
    RAISERROR ('Can only insert 1 row at a time', 16,1)
    ROLLBACK TRANSACTION
    RETURN
    END

    IF (SELECT KeyID FROM inserted) > (SELECT KeyID FROM deleted)
    UPDATE zz_jrTest1
    SET KeyID = CASE WHEN KeyID = (SELECT KeyID FROM deleted)
    THEN (SELECT KeyID FROM inserted)
    ELSE KeyID - 1
    WHERE KeyID BETWEEN (SELECT KeyID FROM inserted) AND (SELECT KeyID FROM
    deleted)

    IF (SELECT KeyID FROM inserted) < (SELECT KeyID FROM deleted)
    UPDATE zz_jrTest1
    SET KeyID = CASE WHEN KeyID = (SELECT KeyID FROM deleted)
    THEN (SELECT KeyID FROM inserted)
    ELSE KeyID + 1
    WHERE KeyID BETWEEN (SELECT KeyID FROM deleted) AND (SELECT KeyID FROM
    inserted)





    "jr" <com> wrote in message
    news:phx.gbl... 


    Jacco Guest

  4. #4

    Default Re: Trigger Help Needed.

    Thank you very much, this is exactly what I needed. With small
    modification it will work the way I want to. This is a line I was missing:

    IF ROWCOUNT = 0 OR TRIGGER_NESTLEVEL() >1 RETURN


    Best Regards,
    John Rychter



    "Jacco Schalkwijk" <co.uk> wrote in message
    news:%23aeJ2Q%phx.gbl... 
    >
    >[/ref]


    jr Guest

  5. #5

    Default Re: Trigger Help Needed.

    >> ... user will have abilities to type the number which indicates the
    column order on a given list or report. <<

    The basic idea of a client/server system is that display is done in the
    front end, never in the database. Let your front end or report writer
    handle the sequential order of the columns.

    And rows are not records -- even tho you seem to be trying to use SQL
    like a sequential file system. I think you need to get some training in
    SQL and C/S systems.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  6. #6

    Default Re: Trigger Help Needed.

    >> I have a table in which one of the columns (KeyID)
    is used to keep order of the records [sic] for display
    purposes: <<

    You do not have a table because there is no key; IDENTITY is a property
    of the physical representation of the data and not an attribute and your
    other columns are all NULL-able. And records exist in sequential files,
    not in SQL; rows are not records. Is this more like what you are trying
    to do?

    CREATE TABLE zz_jrTest1
    (display_order INTEGER NOT NULL PRIMARY KEY,
    key_name CHAR(5) NOT NULL);
     [/ref]
    records [sic], no matter if a new record [sic] is added, deleted, or
    updated. <<

    Tables have no sequence; you are still thinking in terms of procedural
    code and file processing. And triggers are procedural code that you
    write as a last desparate measure because they are so proprietary, so
    slow and represent a return to a record-at-a-time file system.
     [/ref]
    updated at any given time. <<

    REALLY?? How are you going to enforce that business rule? How are you
    going to keep me from going to QA and trashing this whole thing? Where
    are the constraints?
     [/ref]
    this means that this table can have more than 1 record [sic] with KeyID
    = 0. <<

    It would be better to drop the rows instead of copying the old file
    system "deleted flag" from sequential magentic tape systems of the
    1950's into SQL.

    I think that you want code which will let you put a new item into a
    position in a list and push all the other position numbers ahead of it
    by one:

    CREATE PROCEDURE AddItem
    (place INTEGER, new_item CHAR(5))
    BEGIN
    UPDATE zz_jrTest1
    SET display_order = display_order +1
    WHERE display_order >= place;
    INSERT INTO zz_jrTest1 VALUES (place, new_item);
    END;

    -- delete an item and renumber the ordering columns

    CREATE PROCEDURE RemoveItem (place INTEGER)
    BEGIN
    DELETE FROM zz_jrTest1
    WHERE display_order = place;
    UPDATE zz_jrTest1
    SET display_order = display_order -1
    WHERE display_order > place;
    END;

    -- move an item from position x to y
    CREATE PROCEDURE MoveItem
    (old_place INTEGER, new_place INTEGER)
    UPDATE zz_jrTest1
    SET display_order
    = CASE WHEN old_place = new_place -- do nothing
    THEN display_order
    WHEN old_place < new_place
    THEN CASE WHEN display_order >= old_place
    THEN display_order + 1
    WHEN display_order = new_place
    THEN old_place
    ELSE display_order END
    WHEN old_place > new_place
    THEN ...
    ELSE display_order END;
    WHERE display_order BETWEEN old_place AND new_place
    OR display_order BETWEEN new_place AND old_place;

    You can expand out the CASE expressions and optimize them a bit, but you
    get the idea.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  2. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  3. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  4. Trigger key
    By Elisabeth Smith in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:22 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139