From: Marcus Krause <marcus.k79@expires-2004-04-27.arcornews.de>
Subject: Problem with a "blocking period" Trigger
Date: Tue, 27 Apr 2004 11:55:33 +0200
Organization: Hub.Org Networking Services
Lines: 51
Message-ID: <c6laqu$1eka$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.6) Gecko/20040113
To: pgsql-sql@postgresql.org

Since some days i've been working on a "blocking period" trigger.
Before customer's login data will be checked, the system should be
look, if there's set a timetook-FIELD(TIMESTAMP) in the future. Then,
login trial will be denied!
Besides, if login fails due to wrong login data, a
loginattempts-FIELD(integer) will be increased!

The following Trigger calculates the intervaltime & restarts the
loginattempts count!

But somewhere in the code is a syntax error, i cannot find:
ERROR:  syntax error at or near "$1" at character 39

Would you please open my eyes?

I'm open to other scripts handling this situation with a
loginattempts-FIELD(integer) & timetook-FIELD(TIMESTAMP)!

----------WITHIN_A_TRIGGER-----------
DECLARE
  -- sets intervaltime of blocking period
  intervaltime CONSTANT integer:= 3;
  -- sets maximum access trials
  loginattempts CONSTANT integer:= 3;
  tmpinterval interval;
  minutes integer;
BEGIN
  -- blocking condition:
  -- CUST_LOGINATTEMPTS=0 & CUST_TIMETOOK is in future
  tmpinterval:= CURRENT_TIMESTAMP - old.cust_timetook;
  minutes:= extract(minute FROM INTERVAL tmpinterval)::integer;
  -- maximum trials has been made
  -- setting blocking period
  IF old.cust_loginattempts=0 AND old.cust_timetook IS NULL THEN
     UPDATE Customer SET cust_timetook= CURRENT_TIMESTAMP +
(intervaltime::integer || 'hours')::interval
     WHERE cust_id= old.cust_id;
  -- maximum trials has been made & CUST_TIMETOOK is in past
  -- setting blocking period
  ELSIF old.cust_loginattempts=0 AND minutes>0 THEN
     UPDATE Customer SET cust_timetook= CURRENT_TIMESTAMP +
(intervaltime::integer || 'hours')::interval
     WHERE cust_id= old.cust_id;
  ELSIF (new.cust_loginattempts= loginattempts) THEN
     UPDATE Customer SET cust_loginattempts=0
     WHERE cust_id= old.cust_id;
  END IF;
  -- returning irrelevant result
  RETURN NULL;
END;
----------WITHIN_A_TRIGGER-----------