|
--
-- -----------------------------------------------------------------------------
-- 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
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.