Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2009
    Posts
    9

    Question Unanswered: HowTo Buisness Rule-MultiRow GroupTotal=Constant

    Hello to all helpers,

    I have a table in database:

    Create table TestTable (GroupId int, ANumber int)

    with samle data :

    GroupId ANumber
    -------------------
    1 10
    1 70
    1 20
    2 60
    2 40
    3 35
    3 40
    3 15
    3 10


    My Buisness Rule requirement is : Keep Group Total to 100

    like for

    Group 1 >> 10+70+20=100
    Group 2 >> 60+40=100
    Group 3 >> 35+40+15+10=100

    Is it possible to use some constraint ( but it is single row level) ?

    How to implement this buisness rule on SQLServer ?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    try grouping the data by the GroupId
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2009
    Posts
    51

    something like

    --99 will be the number to add in ANUMBER column
    --groupid with low anumber will be tried to filled by ASC
    --groupid incremented by DESC

    INSERT TESTTABLE (GROUPID, ANUMBER)
    SELECT
    (
    SELECT
    CASE WHEN B.GROUPID IS NOT NULL THEN B.GROUPID
    ELSE A.NEWGROUPID
    END AS 'GROUPID'
    FROM
    (SELECT
    TOP 1 (GROUPID) +1 AS 'NEWGROUPID', GROUPID
    FROM TESTTABLE
    ORDER BY GROUPID DESC
    ) A
    LEFT OUTER JOIN
    (SELECT
    TOP 1 GROUPID, SUM(ANUMBER) AS 'E'
    FROM TESTTABLE
    GROUP BY GROUPID
    ORDER BY E ASC
    ) AS B
    ON A.GROUPID = B.GROUPID AND (B.E + 99) < 100
    ) C , 99

    need more testing

  4. #4
    Join Date
    Feb 2009
    Posts
    51

    Tested and fixed an error...

    INSERT TESTTABLE (GROUPID, ANUMBER)
    SELECT
    (
    SELECT CASE WHEN B.GROUPID IS NOT NULL THEN B.GROUPID
    ELSE A.NEWGROUPID
    END AS 'GROUPID'
    FROM (SELECT TOP 1 (GROUPID) +1 AS 'NEWGROUPID', GROUPID
    FROM TESTTABLE ORDER BY GROUPID DESC) A
    LEFT OUTER JOIN
    (SELECT TOP 1 GROUPID
    FROM TESTTABLE
    WHERE GROUPID IN
    (SELECT TOP 1
    CASE WHEN (SUM(ANUMBER) + 99) <= 100 THEN GROUPID
    END AS 'GROUPID'
    FROM TESTTABLE
    GROUP BY GROUPID ORDER BY GROUPID DESC ) AND GROUPID IS NOT NULL
    ) AS B
    ON A.GROUPID = B.GROUPID
    ) C , 99



    Regards
    TS

  5. #5
    Join Date
    Feb 2009
    Posts
    9
    Thanks myle and dbfHelp

    My question was :

    How to prevent application to make insert,modify and delete rows of
    given GroupId which not satisfy Buisness Rule of 100

    (using some constraint, instead of trigger or some other technique)

    My question was NOT how to insert data.

    Regards

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unfortunately, I can't really see you doing this. Your problem is that there are circumstances when your condition will not be true. For example this is your current data:
    Code:
    1         10
    1         70
    1         20
    What if we want to change this to:
    Code:
    1         15
    1         55
    1         30
    The starting point is valid and the end point is valid, but any intermediate steps would be invalid. The only ways I can see how you could do this whilst obeying the rule are ugly. e.g.:
    Insert three new rows like this:
    Code:
    100         15
    100         55
    100         30
    then delete rows where GroupID = 1 and then update GroupId to 1 where it equals 100. Urgh!
    Another option is to disable the constraint, make your changes, and then enable it again. This is not so bad, except the constraint will likely be a function call to an aggregate query, which is inefficient and could cause performance problems if the table is substantial.

    In short - you *can* do this with a trigger or check constraint - the question in a SQL database is "are you sure you want to?".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Akshully - you aren't using SQL Server 2008 by any chance? I wonder if the engine defers constraint checking until the end of a MERGE statement.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2009
    Posts
    9
    Thanks mpootle flump,


    I dont want How to insert/update/delete the grouped rows.
    (It is client applications' responsiability)

    I want:

    How to define such constraint/instead of trigger that keep
    data according to BuisnessRule-100 ? so that client application
    must do insert/update/delete according to rule of 100.


    Thanks

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by angel0in
    I dont want How to insert/update/delete the grouped rows.
    (It is client applications' responsiability)

    I want:

    How to define such constraint/instead of trigger that keep
    data according to BuisnessRule-100 ? so that client application
    must do insert/update/delete according to rule of 100.


    Thanks
    I know. Read my post again. I have discussed the complications of this.

    If you don't understand anything I've written, ask me to explain more thoroughly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I was bored at lunch:
    Code:
    USE test
    GO 
    
    --Create table
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.constrainified')) BEGIN
        DROP TABLE dbo.constrainified
    END
    
    CREATE TABLE dbo.constrainified
        (
              da_pk            INT IDENTITY(1, 1)    NOT NULL
            , GroupId        TINYINT                NOT NULL
            , ANumber        TINYINT                NOT NULL
            , CONSTRAINT pk_constrainified PRIMARY KEY CLUSTERED (da_pk) WITH (FILLFACTOR = 100)
            , CONSTRAINT ck_constrainified_ANumber_1 CHECK (ANumber <= 100)
        )
    GO
    
    IF EXISTS (SELECT NULL FROM sys.indexes WHERE name = N'ix_constrainified_GroupId_ANumber_nu_nc') BEGIN
        DROP INDEX dbo.constrainified.ix_constrainified_GroupId_ANumber_nu_nc
    END
    
    --Index to minimise function overhead
    CREATE NONCLUSTERED INDEX ix_constrainified_GroupId_ANumber_nu_nc
    ON dbo.constrainified (GroupId ASC)
    INCLUDE (ANumber)
    WITH
        (
            FILLFACTOR = 90
        )
    
    --Create function
    IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE type_desc = 'SQL_SCALAR_FUNCTION' AND object_id= OBJECT_ID('dbo.get_dem_ANumbers')) BEGIN
        EXEC('
    CREATE FUNCTION  dbo.get_dem_ANumbers()
    RETURNS INT
    AS
    BEGIN
        RETURN    NULL
    END'
        )
    END
    GO
    
    ALTER FUNCTION dbo.get_dem_ANumbers
    --WITH SCHEMABINDING
        (
            @GroupId        TINYINT
        )
    RETURNS TINYINT
    AS
    BEGIN
    
        RETURN        (SELECT    SUM(ANumber)
                    FROM    dbo.constrainified
                    WHERE    GroupId     = @GroupId)
                    
    
    END
    GO
    
    --Create the constraint
    ALTER TABLE dbo.constrainified
    ADD CONSTRAINT ck_constrainified_ANumber_2 CHECK (dbo.get_dem_ANumbers(GroupId) = 100)
    
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    --Insert data - we need to disable constraint, insert and reenable
    BEGIN TRY --Insert Data
    
        BEGIN TRAN
    
            ALTER TABLE dbo.constrainified 
            NOCHECK CONSTRAINT ck_constrainified_ANumber_2
    
            INSERT INTO dbo.constrainified
            SELECT      GroupId        = 1
                    , ANumber        = 10
            UNION ALL
            SELECT    1, 70
            UNION ALL
            SELECT    1, 20--Change this number to something other than 20 and the update fails.
    
            ALTER TABLE dbo.constrainified 
            WITH CHECK CHECK CONSTRAINT ck_constrainified_ANumber_2
    
        COMMIT TRAN
    
    END TRY --Insert Data
    BEGIN CATCH --Insert Data
    
        RAISERROR('You got yer numbers wrong', 16, 1)
    
        ROLLBACK TRAN
    
    END CATCH --Insert Data
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So you do't have to disable and re-enable, I prefer a trigger for this.

    But just because this is possible, doesn't mean I advocate it!
    Code:
    USE playdb
    GO
    
    CREATE TABLE dbo.your_table (
       group_id int NOT NULL
     , a_number int NOT NULL
    )
    GO
    
    CREATE TRIGGER dbo.your_trigger
      ON dbo.your_table
      AFTER UPDATE, INSERT
    AS
      BEGIN
    
        SET NOCOUNT ON
    
          IF EXISTS (
            SELECT group_id
            FROM   dbo.your_table
            GROUP
                BY group_id
            HAVING Sum(a_number) <> 100
           )
            BEGIN
              ROLLBACK TRAN
              RAISERROR ('An insert/update statement caused the sum of a_number to not equal zero for a given group_id. The batch has been rolledback', 16, 1)
            END
    
        SET NOCOUNT OFF
    
      END
    
    GO
    
    INSERT INTO dbo.your_table (group_id, a_number)
              SELECT 1, 100
    UNION ALL SELECT 2, 75
    UNION ALL SELECT 2, 25
    GO
    
    UPDATE dbo.your_table
    SET    a_number = 101
    WHERE  group_id = 1
    GO
    
    DROP TABLE dbo.your_table
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2009
    Posts
    9
    Thanks again pootle flump,

    Please give me some time to understand

    CONSTRAINT pk_constrainified PRIMARY KEY CLUSTERED (da_pk) WITH (FILLFACTOR = 100)
    I will come back after studing it.


    Thanks

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That line is probably the least interesting in all the code I wrote. Note that the code does not require a primary key, nor a clustered index. I have just assumed you have a primary key on your real table, and that it is also the clustered index. Don't spend long figuring out that bit - it is not central to the answer.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2009
    Posts
    9
    Thanks gvee,

    your solution is good and will work.

    I noticed two things with Triggers.

    1. when Rollback is used, Identity column's serial no skipped

    2. I have seen Microsoft's Accounts software's database. There is not a single Trigger in database. It it means Microsoft not prefer to use Triggers.

    Thanks

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee
    So you do't have to disable and re-enable, I prefer a trigger for this.
    Yes I considered that. A few things:
    You haven't handled deletes.
    How exactly can someone issue updates without this trigger rolling back their transaction?
    This only works if you are handling sets of data - if the interface is sprocs and a row at a time is inserted, you are in trouble.

    Not criticising - just exploring the issue. There isn't a clean way to handle this requirement.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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