I don’t want field for a specific row to not be updated atall

Question

I have a table named users in which there is accountlocked field ... the possible values of that field are Y and N ... if the user from frontend logs in with 3attempts of wrong password then accountlocked will be Y which means account is locked... but I have a specific user that I don’t want the accountlocked to be updated to Y... I created a trigger which can update it to N once it is Y... but I just don’t want the field to be updated to Y atall in the first for that specific user

CREATE OR REPLACE TRIGGER traccountunlock AFTER
   UPDATE OF accountlocked ON users
   FOR EACH ROW
   WHEN (new.username=‘Testuser ’)
BEGIN
    IF :new.accountlocked = 'Y' THEN
        UPDATE users
        SET
            accountlocked = 'N'
        WHERE
            username IN (
                'Testuser'
            );

    END IF;
END;

This works but it does it after the field is updated to Y,but I don’t want it to update to Y at the first place something like instead of ... but instead of works only for views not tables in oracle

Note: I can do it through java code but, I’m not looking for that solution as it needs deploy and future issues

Yes, We know that but we are looking for something through SQL for various reasons. Thanks for looking into this — Techlearner, Sep 19 '19 at 13:35
Like @GMB said you could easily solve your problem in your application. Could you explain why you dont want to do this? I see you dont want becasue of deployment and future issues but could you be a bit more specific — Collin, Sep 19 '19 at 13:45
There are 2 reasons for this . 1.I dont want to hardcode some test users in the prod code 2. If the testing team needs it for a new user or stuff like that we again need to push those changes and deploy it along the sprint process and that again needs a lot of tracking and stuff thats expensive, so looking to decouple this with the help of a trigger or somehting as such from SQL DB @Collin — Techlearner, Sep 19 '19 at 13:49
[You might want to consider this post before you implement bu͞sin͠es̴ś ̶l͡og̨ic̷ in a t҉̸r̷͏i̶̵ģ̷̡g̡e͘҉r͞҉](https://stackoverflow.com/a/32009988/213136) — Bob Jarvis - Слава Україні, Sep 19 '19 at 14:19

score 2 · Answer 1 · edited Sep 19 '19 at 14:29

2

It would be this one. However, I think you should solve it better on application level.

CREATE OR REPLACE TRIGGER traccountunlock 
   BEFORE UPDATE OF accountlocked ON users
   FOR EACH ROW
   WHEN (new.username = 'Testuser')
BEGIN
    IF :new.accountlocked = 'Y' THEN
        :new.accountlocked := 'N';
    END IF;
END;

--Just Updated with the working version

edited Sep 19 '19 at 14:29

Techlearner

3
1

answered Sep 19 '19 at 13:50

Wernfried Domscheit

54,457
9
76
110

Tried this but getting an error Error(2,28): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; indicator @Wernfried Domscheit – Techlearner Sep 19 '19 at 14:01
You need a semicolon after `:new.accountlocked = 'N'` – Bob Jarvis - Слава Україні Sep 19 '19 at 14:16
Both a colon and semi colon – Techlearner Sep 19 '19 at 14:19
Thanks So much for this Wernfried and Bob – Techlearner Sep 19 '19 at 14:24

score 2 · Answer 2 · answered Sep 19 '19 at 14:04

2

What are constraints for?

create table t (
  username varchar2(30),
  accountlocked varchar2(1) check( accountlocked in ('Y','N') ),
  constraint not_lock_testuser check( (username,accountlocked) != (('Testuser', 'Y')) )
);

Done!

Best regards, Stew Ashton

answered Sep 19 '19 at 14:04

Stew Ashton

1,499
9
6

But the problem with this is the update fails as and it throws a 500 in the logs and I dont want that...Thanks Tough – Techlearner Sep 19 '19 at 14:12

I don’t want field for a specific row to not be updated atall

2 Answers2