|
I would call this auditing, unless I have the requirement completely wrong, where you need to track changes but retain only the current information in the table. This is one of the few valid uses for triggers spit.
Use a trigger to fire on inset/update/delete, on insert and delete grab the ID, you don't need each field value here, on update grab the before and after values, the ID on the user and shove them into an audit table.
If you are doing bulk inserts (etl data in) then turn off the triggers before processing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think hell just froze over.
Did you just recommend the use of a trigger? (spit)
|
|
|
|
|
Jörgen Andersson wrote: Did you just recommend the use of a trigger
Yeah I feel dirty, had to do it again in the c# forum for the same reason ugh.
It must be the change in local, I'm on holidays in Cairns, it must be affecting my vitriol levels.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If it's of any consolation, I'm totally agreeing with you not just in general on this subject but also about this exception.
|
|
|
|
|
What you suggest is one of my possibilities but I would like to investigate on the possibility only to allow INSERT and SELECT.
I my case I need to prove that NOBODY can never hack the system and modify a tracking data. A trigger can be deactivated. I would like to GRANT only INSERT and SELECT to all users.
I saw system build for FDA rules where developers or DBA can still have all the power on production database and I saw them removing audit data on production. Wrong and testing data in this case but where is the limit between this and falsification.
|
|
|
|
|
Ok so this has nothing to do with read only, it is about locking down your database with enough security to pacify your clients paranoia. Good luck, delving into the high levels of database security is not a trivial excercise. Personally I would try and get hold of a DBA/expert in the area if your client can justify the expense.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's not about paranoia... unit testing, business and database testing has been for client paranoia and audit validation. In my case it's about respecting law and users protection.
|
|
|
|
|
I have two tables
1. A table USER with a USER_ID, NAME, ...
2. A table STATUS with a STATUS_ID, PEOPLE_FK (foreign key to USER_ID), LABEL, DATE_START...
A USER can only have one active status. I use the DATE_START field to keep the history of all status of the user. I never delete a status. If I create a join between USER and STATUS I can easily get all my users with all they status. BUT I would like to create a query to retrieve all my users with they last status only. I'm still learning SQL. What should I use? Is a select in a select recommended? I would like to avoid the solution of with subquesries.
EDIT -- I searched for a duplicate of this question without success. If this question has been already answered just show me the link. thank you.
modified 22-Jun-14 23:27pm.
|
|
|
|
|
The probably best solution is to add an ACTIVE column to your STATUS table and make an unique index from ACTIVE and USER_ID. So you never ever will be able to set two rows to active state - or you will try and got an SQL error...
It will also make your query simple as you always ask for WHERE ACTIVE = 1...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
CREATE TABLE statuser
([userid] int)
;
INSERT INTO statuser
([userid])
VALUES
(1),
(2)
;
CREATE TABLE stat
([userid] int, [status] int, [startdate] date)
;
INSERT INTO stat
([userid], [status], [startdate])
VALUES
(1, 1, '2012-05-30'),
(1, 2, '2013-06-23')
;
with maxstat as (
select s.userid,max(startdate) startdate
from statuser su
left outer join stat s
on su.userid = s.userid
group by s.userid
)
select s.userid,s.status,s.startdate
from maxstat m
join stat s
on m.userid = s.userid
and m.startdate = s.startdate
Oh, also make a habit of having the same name of the fields in all tables, so if it's called USER_ID in one table don't call it PEOPLE_FK in another.
It's much easier to debug that way.
|
|
|
|
|
What is the cost of a trigger in SQL Server? Could we use them or should we avoid to use them? What are the best practice about triggers? Can we use them as business rules?
I'm trying to check the status of a record. I have a table with job offers. Each job offer can have one or more application with differents status. For simplicity let says step 1, 2, 3, 4 and 5. When an application reach the step 5 I must check I have only one application with status 5 by job offer and I must set all other application to step 4. Am I clear? Can I do that or this kind of complex check with a trigger?
|
|
|
|
|
SQL tables and their relationships/indexes/keys are part of the business logic - so you definitely have business rules on SQL. But, there is a certain level of what to put on SQL and what to save for your code...
Triggers are very powerful to do other things while something happened to one of your tables, but it has a performance penalty. I saw databases where an insert that should take of a fraction of a millisecond took over 2 seconds because of the complex computations and data changes from within a trigger. Also if you got into the trigger things you can easily get a situation where trigger fires trigger that fires trigger...and so on. A very unpleasant situation to deal with when errors occur...
So do not use triggers if you can avoid, try to create stored procedures for complex insert/update scenarios!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: trigger fires trigger that fires trigger...and so on.
With SQL Server there is an inbuilt protection system where you will not get a recursive firing of triggers.
I tested this recently as I was concerned about this sort of behaviour.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
You are talking about recursive triggers. I'm about nested triggers... 
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Does it protect against recursion within your nesting?
|
|
|
|
|
No.
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Bugger. 
I'll keep staying clear from triggers then.
|
|
|
|
|
As far as you can!!!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
|
I'm with the other 2, their arguments are perfectly valid, and here is another, triggers spit are a support nightmare, digging out an error in a nested trigger can drive you nuts.
Triggers spit have their place, they are excellent for auditing and logging changes to your data ONLY.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Team,
I need to compare two tables and do the add/update functionality.If the Table2 have any new records it should be added in the Table1 and if the Table1 have same records which is in Table2, its should be updated.
Table1
ID Name Mark
1 Kishor 56
2 Amit 78
4 Vinay 58
Table2
ID Name Mark
2 Amit 67
6 Raj 89
1 Kishor 98
Output
ID Name Mark
1 Kishor 98
2 Amit 67
4 Vinay 58
6 Raj 89
|
|
|
|
|
It would be useful to know which database you are using. Assuming SQL Server then here is some google foo[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
as we all know that the correct format for date and time data in sqlite is :
2014-06-18 01:00:00
my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks
|
|
|
|
|
You should be storing the data as a datetime TYPE not a string, therefore convert (TryParse) your string input to a datetime and pass it to the database in the correct data type using a paramaterised query.
If it fails the tryparse it is invalid
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You should be storing the data as a datetime TYPE not a string
SQLite does not have a DATETIME datatype. I'd recommend storing it as an integer, as recommend on their website[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
