Implementing Interoperable LDAP Applications

--
-- -----------------------------------------------------------------------------
-- Project  : Implement Generic ClLDAP support
-- Purpose  : To manage and maintain user/accounts in a directory server
-- Author   : Eduardo Sobrino
-- Remarks  : The 'clldaplib.dll' will write a log file where all errors that
--          : where found will be detailed. You should find it in the system32
--          : directory of your OS directory. The name of the file is
--          : 'clldaplib.log'...
-- Date     : Aug/2000
--

USE AbacBill
GO

--
-- -----------------------------------------------------------------------------
-- TABLE   : ClLDAP_Accounts
-- Purpose : Provide to manage and remember last userid used. Since the account
--         : is usually identify uniquely with the Account-No and the UserId 
--         : may change, and the UserId is what we use to identify the DN
--         : 'Distinguished Name' to search and manage a user record in the
--         : directory server, we need to remember it in order to do the needed
--         : updates in the directory when the 'DN' should be change.
--         : This implies that a user record in the directory should be remove
--         : and a new one created when the UserId changes.
-- Date    : Aug/2000  (ESob)
--

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[ClLDAP_Accounts]')
   AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE ClLDAP_Accounts
GO

CREATE TABLE ClLDAP_Accounts (
   Acctno   VARCHAR(20),
   UserId   VARCHAR(20),
   Passwd   VARCHAR(20),
   PrevId   VARCHAR(20),
   LastUpdt DATETIME) ;

-- create a clustered inded for the ClLDAP Accounts table...
CREATE CLUSTERED INDEX ClLDAPAccountsIndx ON ClLDAP_Accounts(Acctno) ;
GO

-- -----------------------------------------------------------------------------
-- Modify billing application so that the ClLDAP procedures are invoked
-- automatically...

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE ID = object_id(N'[dbo].[OnCustomerInsert]')
   AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
   DROP TRIGGER [dbo].[OnCustomerInsert]
GO

CREATE TRIGGER OnCustomerInsert
ON Customers
FOR INSERT
AS
BEGIN
   DECLARE @iCustomerId AS INTEGER,
           @iEMail AS VARCHAR(64),
           @iPasswd AS VARCHAR(20),
           @iUserId AS VARCHAR(30)

   SELECT @iCustomerId=CustomerId,
      @iEMail=EMail,@iPasswd=CustPass FROM INSERTED

   -- from e-mail extract the user-id

   IF @iEmail IS NOT NULL
   BEGIN
      DECLARE @cindex AS INTEGER

      SET @cindex = CHARINDEX('@',@iEmail)-1
      IF @cindex <= 0
         SET @cindex = LEN(@iEmail)
      SET @iUserId = substring(@iEmail,1,@cindex)
   END
   ELSE
      SET @iUserId = ''

   DECLARE @iCustId AS VARCHAR(20)
   SET @iCustId = CAST(@iCustomerId AS CHAR)

   IF NOT EXISTS (SELECT * FROM ClLDAP_Accounts WHERE Acctno=@iCustId)
      INSERT INTO ClLDAP_Accounts (Acctno,UserId,Passwd,PrevId,LastUpdt)
      VALUES (@iCustId,@iUserId,@iPasswd,@iUserId,getdate())
   ELSE
      UPDATE ClLDAP_Accounts
         SET UserId=@iUserId,Passwd=@iPasswd,LastUpdt=getdate()
      WHERE Acctno=@iCustId 
END  -- end of create trigger for insert on Customers table
GO

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE ID = object_id(N'[dbo].[OnCustomerUpdate]')
   AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
   DROP TRIGGER [dbo].[OnCustomerUpdate]
GO

CREATE TRIGGER OnCustomerUpdate
ON Customers
FOR UPDATE
AS
BEGIN
   DECLARE @iCustomerId AS INTEGER,
           @iEMail AS VARCHAR(64),
           @iPasswd AS VARCHAR(20)

   SELECT @iCustomerId=CustomerId,
      @iEMail=EMail,@iPasswd=CustPass FROM INSERTED

   -- from e-mail extract the user-id

   DECLARE @iUserId AS VARCHAR(30)

   IF @iEmail IS NOT NULL
   BEGIN
      DECLARE @cindex AS INTEGER

      SET @cindex = CHARINDEX('@',@iEmail)-1
      IF @cindex <= 0
         SET @cindex = LEN(@iEmail)
      SET @iUserId = substring(@iEmail,1,@cindex)
   END
   ELSE
      SET @iUserId = ''

   DECLARE @iCustId AS VARCHAR(20)
   SET @iCustId = CAST(@iCustomerId AS CHAR)

   IF NOT EXISTS (SELECT * FROM ClLDAP_Accounts WHERE Acctno=@iCustId)
      INSERT INTO ClLDAP_Accounts (Acctno,UserId,Passwd,PrevId,LastUpdt)
      VALUES (@iCustId,@iUserId,@iPasswd,@iUserId,getdate())
   ELSE
      UPDATE ClLDAP_Accounts
         SET UserId=@iUserId,Passwd=@iPasswd,LastUpdt=getdate()
      WHERE Acctno=@iCustId 

END  -- end of create trigger for update on Customers table
GO

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE ID = object_id(N'[dbo].[OnCustomerDelete]')
   AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
   DROP TRIGGER [dbo].[OnCustomerDelete]
GO

CREATE TRIGGER OnCustomerDelete
ON Customers
FOR DELETE
AS
BEGIN
   DECLARE @iCustomerId AS INTEGER,
           @iEMail AS VARCHAR(64),
           @iPasswd AS VARCHAR(20)

   SELECT @iCustomerId=CustomerId,
      @iEMail=EMail,@iPasswd=CustPass FROM DELETED

   DECLARE @iCustId AS VARCHAR(20)
   SET @iCustId = CAST(@iCustomerId AS CHAR)

   DELETE FROM ClLDAP_Accounts WHERE Acctno=@iCustId
END  -- end of create trigger for delete on Customers table
GO