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
loginattemptsCONSTANT 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 --
settingblocking 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_TIMETOOKis in past -- setting blocking period ELSIF old.cust_loginattempts=0 AND minutes>0 THEN UPDATE Customer
SETcust_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
irrelevantresult RETURN NULL;
END;
----------WITHIN_A_TRIGGER-----------