Re: using lookup and display only field as control field

(jim.murphy@xxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: using lookup and display only field as control field
    ... unassociated table to control what date is entered in another table. ... I am thinking of using display only field and lookup to set it ... I can't see how a NULL key field would help you. ... MS SQL Server, so the syntax you would get in this newsgroup may not ...
    (comp.databases.ms-sqlserver)
  • Store program EXE in table?
    ... I have remote control software ... my application uses as its data storage system MSDE 2000 (with ... since I already have SQL Server running on the network I'd like ... Is there some way that I can either a) store my program files (one EXE ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Server 2000 speed problems
    ... >> CREATE TABLE Control ... >> This is done by inserting a new row with the current time into the ... >> written to the database, and the writing needs to be fast, in the region ... >> similar that can be changed on SQL Server that could help solving this ...
    (microsoft.public.sqlserver.programming)
  • Re: DBA Access to SQL Server
    ... > look at the qualifications or trust of your sql dba ... > rather than his/her permissions. ... > 99.9% of the time i hear this question it's a control ... if your support model includes the DBA folks doing the SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: :: Control user access
    ... IMO it totally depends on the nature of the application. ... As you indicate SQL Server involvement, ... Microsoft MVP (Windows Security) ... > way to control user access in my application. ...
    (microsoft.public.security)