--------------------------------------------------------------------------------
--
--   Copyright (c) 2009 Software Gems Pty Ltd
--   Derek Asirvadem  06 Jan 09
--
--------------------------------------------------------------------------------
-- Limit Count of Child Rows
--    Function code
--------------------------------------------------------------------------------
-- Note
--   - Declarative Referential Integrity is used throughout
--   - All Constraints are implemented Declaratively
--   - Using SQL-92
--   - Tested on Sybase ASE 15.0.2
--
--   - The CHECK Constraint in its vanilla form allows the inserted row (only)
--     to be checked
--   - When the inserted row needs to be CHECKed against other rows (in either
--     the same table, or another table), that code is placed in a Function,
--     which is called by the CHECK Constraint
--   - Although SQL is supposed to allow a subquery in the CHECK, not all
--     platforms provide it.
--------------------------------------------------------------------------------
-- Related:
--   DDL: http://www.softwaregems.com.au/Documents/Tutorial/Child_Count.sql
--------------------------------------------------------------------------------

--------------------
-- Count Function --
--------------------

DROP   FUNCTION Child_Count_fn
GO

CREATE FUNCTION Child_Count_fn (
--
-- Function: Return Child Count for Parent
--
        @ParentKey    CHAR(4)               -- First component of Child Key
        )                                   -- identifies Parent for which to count
    RETURNS SMALLINT
AS
    RETURN ( SELECT COUNT( ChildNo )
        FROM  Child
        WHERE ParentKey = @ParentKey
        )
GO
