February 19, 2009 at 11:01 am
Hi.
I need to provide functionality to maintain a history (Effective date to Superseded date) of the relationship between a company and an asset (in this, case a coal mine).
My database is SQL Server 2005 and my UI is in MS Access.
Only one company at a time may own a mine - therefore there should only be one record for each mine with a NULL Superseded date (the company which currently owns the mine) and date ranges may not overlap.
The attached text file is the code I've cobbled together so far (I tried to post it here but I kept getting errors - too much code I think.).
My first question is - is this the right way to do this or is there a better way?
Second - When I try to make certain edits (which should pass all of the validation), I get an error dialog from MS Access (see the attached .bmp file). I think that perhaps this is happening because the code inside my INSTEAD OF triggers is making changes which Access is detecting as another user making a change. There's no way that I can think of to resolve this.
February 19, 2009 at 11:18 am
I think I've figured out at least part of the problem (maybe all of it). The UPDATE statement in my INSTEAD OF UPDATE trigger is not right. I'm working on figuring out how it should be...
February 19, 2009 at 7:05 pm
Hi.
I've tried to take a slightly different tack and am now having a different problem. I'm trying to use a function to perform a check constraint. Logically, I think that it should work but I'm getting an error on the check constraint and can't figure out why.
Any help would be appreciated. Here's the code to build what I'm working with.
-----------------------------------------------------------------------------------------------
-- GET STAFF_ID (User defined function)
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Get_Staff_Id',N'FN') IS NOT NULL DROP FUNCTION Get_Staff_Id
GO
CREATE FUNCTION Get_Staff_Id() RETURNS INT AS
BEGIN
RETURN 1 --(SELECT Staff_Id FROM [QryDbCorp\qry1].Corporate_Query.dbo.EUB_Staff WHERE Staff_User_Id = RIGHT(suser_sname(),5))
END
GO
-----------------------------------------------------------------------------------------------
-- Mine
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
IF OBJECT_ID(N'Mine',N'U') IS NOT NULL DROP TABLE Mine
CREATE TABLE Mine (
Mine_Number INT
,Mine_Name VARCHAR(20)
,CONSTRAINT PK_MINE PRIMARY KEY CLUSTERED (Mine_Number)
,CONSTRAINT UK_Mine_Name UNIQUE (Mine_Name))
INSERT INTO MINE VALUES (1, 'Mine number 1')
INSERT INTO MINE VALUES (2, 'Mine number 2')
INSERT INTO MINE VALUES (3, 'Mine number 3')
-----------------------------------------------------------------------------------------------
-- Company
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
IF OBJECT_ID(N'Company',N'U') IS NOT NULL DROP TABLE Company
CREATE TABLE Company (
Company_Number INT
,Company_Name VARCHAR(20)
,CONSTRAINT PK_Company PRIMARY KEY CLUSTERED (Company_Number)
,CONSTRAINT UK_Company_Name UNIQUE (Company_Name))
INSERT INTO Company VALUES (1, 'Company 1')
INSERT INTO Company VALUES (2, 'Company 2')
INSERT INTO Company VALUES (3, 'Company 3')
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
IF OBJECT_ID(N'Check_Valid_Date',N'FN') IS NOT NULL DROP FUNCTION Check_Valid_Date
GO
CREATE FUNCTION Check_Valid_Date(@Mine Int, @Date DateTime) RETURNS INT AS
BEGIN
DECLARE @Result INT
IF EXISTS (SELECT * FROM Mine_Owner WHERE Mine_Number = @Mine AND @Date >= Effective_Date AND @Date <= Superseded_Date)
SET @Result = 1
ELSE
SET @Result = 0
RETURN @Result
END
GO
-----------------------------------------------------------------------------------------------
-- Mine_Owner
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
CREATE TABLE Mine_Owner (
RecNo INT IDENTITY
,Mine_Number INT NOT NULL
,Company_Number INT NOT NULL
,Effective_Date SMALLDATETIME NOT NULL
,Superseded_Date SMALLDATETIME NULL
,Update_Date_Time DATETIME NULL
,Updated_By_Id INT NULL
,CONSTRAINT PK_Mine_Owner PRIMARY KEY CLUSTERED (Mine_Number, Effective_Date)
,CONSTRAINT FK_Mine_Owner_to_Mine FOREIGN KEY (Mine_Number) REFERENCES Mine (Mine_Number) ON UPDATE CASCADE
,CONSTRAINT FK_Mine_Owner_to_Company FOREIGN KEY (Company_Number) REFERENCES Company (Company_Number) ON UPDATE CASCADE
,CONSTRAINT UK_Current_Mine_Owner UNIQUE (Mine_Number, Superseded_Date)
,CONSTRAINT CK_Superseded_Date_ge_Effective_Date CHECK (Superseded_Date >= Effective_Date)
,CONSTRAINT CK_Valid_Effective_Date CHECK (dbo.Check_Valid_Date(Mine_Number, Effective_Date) = 0)
,CONSTRAINT CK_Valid_Superseded_Date CHECK (dbo.Check_Valid_Date(Mine_Number, Superseded_Date) = 0))
February 23, 2009 at 1:42 pm
After much gnashing and trashing I think I've got this solved. Here's the code for posterity sake. Maybe it'll help someone else some day.
-----------------------------------------------------------------------------------------------
-- GET STAFF_ID (User defined function)
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Get_Staff_Id',N'FN') IS NOT NULL DROP FUNCTION Get_Staff_Id
GO
CREATE FUNCTION Get_Staff_Id() RETURNS INT AS
BEGIN
RETURN 1 --(SELECT Staff_Id FROM [QryDbCorp\qry1].Corporate_Query.dbo.EUB_Staff WHERE Staff_User_Id = RIGHT(suser_sname(),5))
END
GO
-----------------------------------------------------------------------------------------------
-- Mine
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
IF OBJECT_ID(N'Mine',N'U') IS NOT NULL DROP TABLE Mine
CREATE TABLE Mine (
Mine_Number INT
,Mine_Name VARCHAR(20)
,CONSTRAINT PK_MINE PRIMARY KEY CLUSTERED (Mine_Number)
,CONSTRAINT UK_Mine_Name UNIQUE (Mine_Name))
INSERT INTO MINE VALUES (1, 'Mine number 1')
INSERT INTO MINE VALUES (2, 'Mine number 2')
INSERT INTO MINE VALUES (3, 'Mine number 3')
-----------------------------------------------------------------------------------------------
-- Company
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
IF OBJECT_ID(N'Company',N'U') IS NOT NULL DROP TABLE Company
CREATE TABLE Company (
Company_Number INT
,Company_Name VARCHAR(20)
,CONSTRAINT PK_Company PRIMARY KEY CLUSTERED (Company_Number)
,CONSTRAINT UK_Company_Name UNIQUE (Company_Name))
INSERT INTO Company VALUES (1, 'Company 1')
INSERT INTO Company VALUES (2, 'Company 2')
INSERT INTO Company VALUES (3, 'Company 3')
-----------------------------------------------------------------------------------------------
-- Check_Overlapping_Ranges (User defined function)
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
IF OBJECT_ID(N'Check_For_Overlapping_Date_Ranges',N'FN') IS NOT NULL DROP FUNCTION Check_For_Overlapping_Date_Ranges
GO
CREATE FUNCTION Check_For_Overlapping_Date_Ranges(@RecNo INT, @Mine INT, @Begin_Date DATETIME, @End_Date DATETIME) RETURNS INT AS
BEGIN
DECLARE @Result INT
SET @Result = 0
IF EXISTS (
SELECT
*
FROM Mine_Owner
WHERE
Mine_Number = @Mine
AND RecNo <> @RecNo
AND ( (@Begin_Date BETWEEN Effective_Date AND Superseded_Date)
OR (@End_Date BETWEEN Effective_Date AND Superseded_Date)
OR (Effective_Date BETWEEN @Begin_Date AND @End_Date)
)
)
SET @Result = 1
ELSE
SET @Result = 0
RETURN @Result
END
GO
-----------------------------------------------------------------------------------------------
-- Mine_Owner
-----------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Mine_Owner',N'U') IS NOT NULL DROP TABLE Mine_Owner
CREATE TABLE Mine_Owner (
RecNo INT IDENTITY
,Mine_Number INT NOT NULL
,Company_Number INT NOT NULL
,Effective_Date SMALLDATETIME NOT NULL
,Superseded_Date SMALLDATETIME NULL
,Update_Date_Time DATETIME NULL
,Updated_By_Id INT NULL
,CONSTRAINT PK_Mine_Owner PRIMARY KEY CLUSTERED (Mine_Number, Effective_Date)
,CONSTRAINT FK_Mine_Owner_to_Mine FOREIGN KEY (Mine_Number) REFERENCES Mine (Mine_Number) ON UPDATE CASCADE
,CONSTRAINT FK_Mine_Owner_to_Company FOREIGN KEY (Company_Number) REFERENCES Company (Company_Number) ON UPDATE CASCADE
,CONSTRAINT UK_Current_Mine_Owner UNIQUE (Mine_Number, Superseded_Date)
,CONSTRAINT CK_Superseded_Date_ge_Effective_Date CHECK (Superseded_Date >= Effective_Date)
,CONSTRAINT CK_Overlapping_Date_Range CHECK (dbo.Check_For_Overlapping_Date_Ranges(RecNo, Mine_Number ,Effective_Date, Superseded_Date) = 0))
GO
CREATE TRIGGER Mine_Owner_INSERT_UPDATE ON Mine_Owner FOR INSERT, UPDATE AS
UPDATE t1 SET
Company_Number = i.Company_Number
,Effective_Date = i.Effective_Date
,Superseded_Date = i.Superseded_Date
,Update_Date_Time = GETDATE()
,Updated_By_Id = dbo.Get_Staff_Id()
FROM Mine_Owner t1
JOIN inserted i
ON t1.RecNo = i.RecNo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply