From: Marcus Krause <marcus.k79@expires-2004-04-27.arcornews.de>
To: pgsql-sql@postgresql.org
Subject: Problem with a "blocking period" Trigger
Date: Tue, 27 Apr 2004 11:55:33 +0200
Message-ID: <c6laqu$1eka$1@news.hub.org> (raw)
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-----------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: marcus.k79@expires-2004-04-27.arcornews.de
Subject: Re: Problem with a "blocking period" Trigger
In-Reply-To: <c6laqu$1eka$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox