(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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx