Insert Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert Trigger

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-02-19 : 08:52:02

Guys,

I have 15 tables where the combination of CODE, range for EFFECTIVE DATE and END DATE columns should be unique.

For example

ID CD EFF DT END DT
______________________________________
1 CO 02/02/08 02/15/08
2 CO 02/04/09 02/29/08
3 CO 02/16/08 02/29/08

In the above example the table should throw an error for 2nd row ID = 2 since the effective and end date overlap with
1st row ID = 1

Is there any way to accomplish this using triggers on the table?

Any suggestions and inputs would help

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 09:06:56

Yeah. you could right a INSTEAD OF INSERT trigger which checks the inserted date value against the last inserted date value for same code and raise error if same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 09:15:29

Something like:-

CREATE TRIGGER DateCheck ON YourTable
INSTEAD OF INSERT AS
IF EXISTS(SELECT * FROM INSERTED i
          INNER JOIN YourTable t
          ON t.CD=i.CD
          AND (t.EndDt=i.EndDt
          OR t.EffDt=i.EffDt))

RAISERROR ('Duplicate date value',
               16, 
               1 
               );

ELSE
BEGIN
 INSERT INTO YourTable
 SELECT * FROM INSERTED
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:23:29

Or a normal trigger which also would handle malicious updates.
Or a simple CHECK constraint?

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 09:32:40

Peter,

How a CHECK constraint would help here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:48:06

[code]CREATE TRIGGER trgDateCheck
ON YourTable
FOR UPDATE,
INSERT
AS

IF EXISTS (
SELECT *
FROM YourTable AS s
CROSS JOIN YourTable AS x
WHERE x.ID <> s.ID
AND x.CD = s.CD
AND x.BeginDate <= s.EndDate
AND x.EndDate >= s.BeginDate
)
RAISERROR('Overlapping date range found.', 16, 1)[/code]

EDIT: For completeness

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:49:04

You could make the select statement in my previous post as a function which returns 1 or 0.
Then make that function part of the check constraint.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 09:56:43

Thanks Peter, got it!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:57:43

[code]-- Prepare sample table
CREATE TABLE tblSample (ID INT, Campaign VARCHAR(2), BeginDate DATETIME, EndDate DATETIME)
GO

-- Prepare sample function
CREATE FUNCTION dbo.fnCheckDateRange
(
)
RETURNS TINYINT
AS
BEGIN
RETURN CASE
WHEN EXISTS (
SELECT *
FROM tblSample AS s
CROSS JOIN tblSample AS x
WHERE x.ID <> s.ID
AND x.CD = s.CD
AND x.BeginDate <= s.EndDate
AND x.EndDate >= s.BeginDate
)
THEN 1
ELSE 0
END
END
GO

-- Add the function as a constraint
ALTER TABLE tblSample
WITH CHECK
ADD CONSTRAINT CK_tblSample
CHECK (dbo.fnCheckDateRange() = 0)
GO

-- Insert some sample data
SET DATEFORMAT MDY

INSERT tblSample
SELECT 1, 'CO', '02/02/08', '02/15/08' UNION ALL
SELECT 3, 'CO', '02/16/08', '02/29/08'
GO

-- Insert some more sample data
SET DATEFORMAT MDY

INSERT tblSample
SELECT 2, 'CO', '02/04/08', '02/29/08'[/code]

EDIT: For completeness

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-19 : 10:37:31

That is a neat solution Peter. I would modify it slightly to avoid unnecessary work by the check constraint. Specifically, limit the check to the CD value of the row(s) being modified:


-- Prepare sample table
CREATE TABLE tblSample (ID INT, CD VARCHAR(2), BeginDate DATETIME, EndDate DATETIME)
GO

-- Prepare sample function
CREATE FUNCTION dbo.fnCheckDateRange(@CD varchar(2))
RETURNS TINYINT
AS
BEGIN
	RETURN	CASE
			WHEN EXISTS	(
						SELECT		*
						FROM		tblSample AS s
						INNER JOIN	tblSample AS x
									on x.CD = s.CD
						WHERE		x.ID <> s.ID
								AND x.BeginDate <= s.EndDate
								AND x.EndDate >= s.BeginDate
					)
			THEN 1
			ELSE 0
		END
END
GO

-- Add the function as a constraint
ALTER TABLE	tblSample
WITH CHECK
ADD CONSTRAINT	CK_tblSample
CHECK		(dbo.fnCheckDateRange(CD) = 0)
GO

-- Insert some sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	1, 'CO', '02/02/08', '02/15/08' UNION ALL
SELECT	3, 'CO', '02/16/08', '02/29/08'
GO

-- Insert some more sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	2, 'CO', '02/04/08', '02/29/08'

EDIT:
Added red]

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 10:39:23

Good advice. Maybe you should use the @CD parameter in a WHERE clause in the function?

-- Prepare sample table
CREATE TABLE	tblSample
		(
			ID INT,
			CD VARCHAR(2),
			BeginDate DATETIME,
			EndDate DATETIME
		)
GO

-- Prepare sample function
CREATE FUNCTION dbo.fnCheckDateRange
(
	@CD VARCHAR(2)
)
RETURNS TINYINT
AS
BEGIN
	RETURN	CASE
			WHEN EXISTS	(
						SELECT		*
						FROM		tblSample AS s
						INNER JOIN	tblSample AS x ON x.CD = s.CD
						WHERE		s.CD = @CD
								AND x.ID <> s.ID
								AND x.BeginDate <= s.EndDate
								AND x.EndDate >= s.BeginDate
					)
			THEN 1
			ELSE 0
		END
END
GO

-- Add the function as a constraint
ALTER TABLE	tblSample
WITH CHECK
ADD CONSTRAINT	CK_tblSample
CHECK		(dbo.fnCheckDateRange(CD) = 0)
GO

-- Insert some sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	1, 'CO', '02/02/08', '02/15/08' UNION ALL
SELECT	3, 'CO', '02/16/08', '02/29/08'
GO

-- Insert some more sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	2, 'CO', '02/04/08', '02/29/08'

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-19 : 10:45:50

Ah - good catch

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 10:52:58

Or even reduce the need for a self-join completely?

-- Prepare sample table
CREATE TABLE	tblSample
		(
			ID INT,
			CD VARCHAR(2),
			BeginDate DATETIME,
			EndDate DATETIME
		)
GO

-- Prepare sample function
CREATE FUNCTION dbo.fnCheckDateRange
(
	@ID INT,
	@CD VARCHAR(2),
	@BeginDate DATETIME,
	@EndDate DATETIME
)
RETURNS TINYINT
AS
BEGIN
	RETURN	CASE
			WHEN EXISTS	(
						SELECT		*
						FROM		tblSample
						WHERE		ID <> @ID
								AND CD = @CD
								AND BeginDate <= @EndDate
								AND EndDate >= @BeginDate
					)
			THEN 1
			ELSE 0
		END
END
GO

-- Add the function as a constraint
ALTER TABLE	tblSample
WITH CHECK
ADD CONSTRAINT	CK_tblSample
CHECK		(dbo.fnCheckDateRange(ID, CD, BeginDate, EndDate) = 0)
GO

-- Insert some sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	1, 'CO', '02/02/08', '02/15/08' UNION ALL
SELECT	3, 'CO', '02/16/08', '02/29/08'
GO

-- Insert some more sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	4, 'CO', '01/01/08', '02/01/08'

-- Insert some more sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	2, 'CO', '02/04/08', '02/29/08'

-- Insert some more sample data
SET DATEFORMAT MDY

INSERT	tblSample
SELECT	5, 'YY', '01/01/01', '12/31/09'

-- Show table records
SELECT	*
FROM	tblSample

E 12°55'05.25"
N 56°04'39.16"

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-02-19 : 15:24:48

Peso,

Since I have to create check constraint on 15 tables, I want to use the same function for all 15 tables and have different check constraints.

I tried to alter the function to accept table name as variable

ALTER FUNCTION dbo.fnCheckDateRange
(
@tablename varchar(30),
@ID INT,
@CD VARCHAR(2),
@BeginDate DATETIME,
@EndDate DATETIME
)
RETURNS TINYINT
AS
BEGIN
RETURN CASE
WHEN EXISTS (
SELECT *
FROM @tablename
WHERE ID <> @ID
AND CD = @CD
AND BeginDate <= @EndDate
AND EndDate >= @BeginDate
)
THEN 1
ELSE 0
END
END
GO

I get the following error

Msg 1087, Level 15, State 2, Procedure fnCheckDateRange, Line 15
Must declare the table variable "@tablename".

Is there any way around this issue so that I can use a single function

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 00:42:20

quote:
Originally posted by scelamko

Peso,

Since I have to create check constraint on 15 tables, I want to use the same function for all 15 tables and have different check constraints.

I tried to alter the function to accept table name as variable

ALTER FUNCTION dbo.fnCheckDateRange
(
@tablename varchar(30),
@ID INT,
@CD VARCHAR(2),
@BeginDate DATETIME,
@EndDate DATETIME
)
RETURNS TINYINT
AS
BEGIN
RETURN CASE
WHEN EXISTS (
SELECT *
FROM @tablename
WHERE ID <> @ID
AND CD = @CD
AND BeginDate <= @EndDate
AND EndDate >= @BeginDate
)
THEN 1
ELSE 0
END
END
GO

I get the following error

Msg 1087, Level 15, State 2, Procedure fnCheckDateRange, Line 15
Must declare the table variable "@tablename".

Is there any way around this issue so that I can use a single function

Thanks

You cant use variable for table name directly. you need to use dynamic sql if you want this to be done so that the stement inside IF EXISTS becomes,

EXEC('SELECT * FROM '+ @tablename +' WHERE ID <> '+CAST(@ID AS varchar(4))+' AND CD = ''' + @CD+''' AND BeginDate <= '''+ @EndDate+''' AND EndDate >= '''+ @BeginDate + '''')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 03:56:31

For performance, create 15 functions and 15 check constraints.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources