How should i work in this scenario . Should I use Trigger or Leave on User to manage

Question

I am creating an application in which i am using stored procedure where i m implementing my logic.

Now what i need to know is that- i want my database not to contain any invalid entry, for this purpose should i create triggers, for implementing my data validation logic such that when FailPasswordAttemptCount is changed to some value then should i make changes in corresponding column IsLocked accordingly thru triggers or leave it on dba to manage.

eg

if FailPassowrdAttemptCount > 3
  IsCaptchaActivated=True
if FailPasswordAttemptCount>6
  IsLocked=true

now if a dba changes the value of FailPasswordAttemptCount to 4 without changing IsCaptchaActivated to true then this will make an invalid entry for my frontend. SO should i manage it thru triggers or should i left it over dba to make correct entry.

Although this type of entry is not possible thru frontend but in case any1 having privilages to access database, changes directly thru database. For this should i leave it on user or should i manage thru triggers. I want to make my database to remain consistent in all circumstances.

Answer 1

I wouldn't use a trigger for something like this. Triggers are obscure and can be hard to debug for the developer. Use your tables and stored procedures to deal with the issue. Use triggers for when you don't have an alternative.

Answer 2

For this type of situation, I would probably not use a trigger, just for the situation you describe. Though I would wonder why you have dba's manually altering data in a field that closely tied to the security of your app.

Answer 3

I would implement this in the application logic. When calling the login sproc you can return both whether it succeeded as well as the number of failed password attempts and if captcha is needed. Regardless of if the DBA changes the 3 to a 4, your code will see the 4, ignore the result of the validation and present the user with a captcha. If you're worried about DBA's modifying the code directly you can also check the APP_NAME() function/variable to see what program is trying to modify the data. Its something to be very careful with but so is DBAs modifying fields directly.

Answer 4

I'd make the following:

• Put the data validation logic into a stored procedure
• Made the stored procedure the only way the application interacts with the table
• Put the code you want into the stored procedure.

Trigger-based programming paradigma grows too hard to code and maintain as the business logic complexity of your application increases.

However, if you are absolutely sure you will only have the simple logic like this, it is OK to put it into a trigger since this will require minimal changes in the ways the application interacts with the database.

Answer 5

I would use a combination of both. I will try to restrict data as far as possible. And will fire trigger, so that no one can insert any invalid entry.