temporal trigger writing fun
-
Hi this is a trigger I have been writing, as it is it is meant to prevent against duplication and contradiction (and eventually circumlocution, if i get that far
)The function simply returns a boolean based on whether the time intervals provided share any time points.
CREATE TRIGGER dupcontcirc ON lecturer_email_during
INSTEAD OF INSERT
AS
IF (EXISTS (SELECT * FROM INSERTED i
WHERE LID IN
(SELECT LID FROM lecturer_email_during l
WHERE
i.email = l.email
AND
dbo.intersects(i.during, l.during) = 1)))
RAISERROR('duplicate tuples in database',16,1)
IF (EXISTS (SELECT * FROM INSERTED i
WHERE LID IN
(SELECT LID FROM lecturer_email_during l
WHERE
i.email NOT LIKE l.email
AND
dbo.intersects(i.during, l.during) = 1)))
RAISERROR('contradicting tuples in database',16,1)
IF
INSERT INTO (l.LID, l.email, l.during)
SELECT LID, email, during
FROM INSERTED
END
Errors are being thrown up near 'INSERT' and near 'END'
any help appreciated! Cheers Ehsan
-
Please post the table definition and some sample data that makes this fail so we can write the trigger for you.
-
schema for lecturer_email_during
CREATE TABLE
dbo.lecturer_email_during (
LID
int NOT NULL ,
email
char (30) NOT NULL ,
during INTERVAL_DATE
NOT NULL
)
----------------------------------------------------
data to be entered
1, bighead@hotmail.co.uk, [01/01/1990: 01/01/1995]
---------------------------------------------------
I have got it down to one error now with this trigger code
CREATE TRIGGER dupcontcirc ON lecturer_email_during
INSTEAD OF INSERT
AS
IF (EXISTS (SELECT * FROM INSERTED i
WHERE LID IN
(SELECT LID FROM lecturer_email_during l
WHERE
i.email = l.email
AND
dbo.intersects(i.during, l.during) = 1)))
RAISERROR('duplicate tuples in database',16,1)
IF (EXISTS (SELECT * FROM INSERTED i
WHERE LID IN
(SELECT LID FROM lecturer_email_during l
WHERE
i.email NOT LIKE l.email
AND
dbo.intersects(i.during, l.during) = 1)))
RAISERROR('contradicting tuples in database',16,1)
ELSE
INSERT INTO lecture_email_during(LID, email, during)
SELECT LID, email, during
FROM INSERTED
END
the error thrown up is 'incorrect syntax near end'
ps i hope this help, if you need info about any other tables feel free
Cheers Ehsan
-
There's no 'BEGIN' to match the 'END'. What do you expect the 'END' to be 'ending'?
And then again, I might be wrong ...
David Webb -
oh jeeze cant believe I was that stupid...I dont know what I hate more me or the error messages that server 2000 spits out, near what?!, near what?! Damn you
Sorry about that, Ehsan
-
Sometimes it just takes another pair of eyes. We've all stared at a non-functional piece of t-sql and missed the obvious. It keeps us all humble.
And then again, I might be wrong ...
David Webb -
What is the datatype for "INTERVAL_DATE" of colummn "during"?
What is function dbo.intersects ?
Just curious
SQL = Scarcely Qualifies as a Language
-
Ya, I'm pretty sure you don't need a function to make this trigger work.
-
Couldn't see the forest for the circumlocution
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply