|
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[^]
|
|
|
|
|
Eddy Vluggen wrote: SQLite does not have a DATETIME datatype
Now that I did not know.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Eddy Vluggen wrote: SQLite does not have a DATETIME datatype
Hard to believe, but true. Poor guys who have to deal with such crap!
|
|
|
|
|
Bernhard Hiller wrote: Hard to believe, but true. Poor guys who have to deal with such crap!
I prefer this crap over SqlCe, MS Access, XML and Excel as a single-file database.
A DateTime is usually merely a double; a boolean is usually merely a bit. SQLite is lightweight; you won't find a XML, GUID nor a Spatial datatype either - because all those types can be derived from what is already there.
The only thing that seemed weird is that the bool is associated with a numeric type, and not an integer-type.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want.
And have you looked at Sql Server LocalDB?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: And have you looked at Sql Server LocalDB?
I tried, but failed at downloading[^] it.
PIEBALDconsult wrote: I'll stick with SqlCe when given a choice; better to have features I don't use
than miss features I want.
So you'll be replacing all those SqlCe's with Sql-express versions?
SqlCe and SQLite are a close match; but I like to run my code as well on my OpenSUSE desktop and there's no SqlCe there 
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
