|
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[^]
|
|
|
|
|
Eddy Vluggen wrote: replacing all those SqlCe's with Sql-express versions
No. The other way around. 
I use Express for a few things, but for one of my projects I wanted to be able to have the app on a flash drive and not require a server/service/daemon running. That way it can be carried around and used whereever. So now I allow the app to use Ce or regular Sql Server, whichever the user wants to use. All the tables and SQL statements are the same (including DATEs and GUIDs), only the Provider is different.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
..and might even work relative happily using a different provider[^] on a Raspberry Pi
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Bernhard Hiller wrote: have to deal with such crap
Seems a choice to me.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
No, there isn't. You validate it in code before inserting/updating.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Steps:
Choose the data type that it is actually going to be stored as.
Take a value and use on of the sqllite functions to convert it to a different type.
Take that value and convert it back to the original type.
If they are the same then it is valid.
So if you start with text, convert it to a number, then convert the number back to text and compare the original text and the final text.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
