temporal trigger writing fun

Question

Post reply

temporal trigger writing fun

Ehsan Ashouri

SSC Veteran

Points: 241
More actions
October 27, 2005 at 10:11 am

#169332

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post the table definition and some sample data that makes this fail so we can write the trigger for you.

Ehsan Ashouri SSC Veteran Points: 241 More actions · Answer 2

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

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

Ehsan Ashouri SSC Veteran Points: 241 More actions · Answer 4

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

What is the datatype for "INTERVAL_DATE" of colummn "during"?

What is function dbo.intersects ?

Just curious

SQL = Scarcely Qualifies as a Language

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ya, I'm pretty sure you don't need a function to make this trigger work.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

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)