|
|
Posted by Erland Sommarskog on 10/02/21 11:48
(jim.murphy@travelinsurance.ca) writes:
> Thanks for reply . I believe there was a post from someone else here
> on Informix lookups. At the moment just brainstorming so thought I
> would try a post here and see what I could get. Perhaps someone has
> done something similar. One idea was:
> The control table would have only one row. Since there is no explicit
> assosciation between the two tables if the key field was null in both
> tables the date field in the control table could be looked up
>
> Will see if I get anything from your suggestion
With a single-row table it's easy as I said. You would use a cross
join:
CREATE TRIGGER jimstrigger ON tbl AFTER INSERT, UPDATE AS
IF EXISTS (SELECT *
FROM inserted i
CROSS JOIN controltable c
WHERE i.date_enter <= c.controldate)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Date_entered before controldate not permitted!, 16, 1)
RETURN
END
"inserted" is a virtual table that holds the inserted rows.
Of course, the syntax above is specific to SQL Server, but it's the
only RDBMS I know.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|