CODEWITHSUNDEEP
sql
billsecond
billsecond

Reputation: 612

SQL Query Record Flag

When a SQL record gets updated, I want a record such as a single bit, char or int that acts as a flag and that will be null, off, or 0.

Then I want to have a different application mark that record with a flag once it passes through it.

However, if I manually update the record, or via a SP, I want the flag to reset.

I would prefer not to use a trigger, or modify Stored procedures to do this, but how would I get a flag type field that would be updated?

Upvotes: 1

Views: 53

Answers (1)

MatBailie
MatBailie

Reputation: 86706

You've just eliminated the two standard practices.

Triggers allow forced consistency, the only way to circumvent them is to explicitly turn them off.

Stored Procedures allow you to expose an API to different users/logins/groups/etc; if a write activity can only happen through the API then you've ensured you get the desired behavior (much like methods on objects, where the database is the object). Then, an admin-user could have direct access to the tables to envoke different behavior, or have access to different SPs with different behavior.

Normally I'd advocate SPs. It's generally a bad idea to allow a human direct access to an application's database/tables. Eventually they WILL forget a WHERE clause.

The only other option I can think of is to force all access to through the application and effectively build the behavior and access controls in to that application. Essentially migrating SPs into the application.

Upvotes: 2

Related Questions