InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 21:34:22 GMT20http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Sergiy (4/9/2013)[/b][hr][quote][b]quackhandle1975 (4/9/2013)[/b][hr]I should have been clearer, I was not saying avoid triggers.[/quote]Yes, this one looks very different. :-)They say - consider environment. Look at the name of the article you're putting your comment to. Whatever you (me, whoever) put in here would be interpreted in this particular context.[quote] That would be a daft call.[/quote]Agreed. But this is exactly the call made in the article.And seemingly promoted by opc.three to "best practices", if I understand him right.Hm, daft...:cool:[/quote]If I were you I would not bother trying to put words in others mouths. You obviously have enough trouble controlling the ones coming out of your own mouth.Tue, 09 Apr 2013 16:01:20 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]quackhandle1975 (4/9/2013)[/b][hr]I should have been clearer, I was not saying avoid triggers.[/quote]Yes, this one looks very different. :-)They say - consider environment. Look at the name of the article you're putting your comment to. Whatever you (me, whoever) put in here would be interpreted in this particular context.[quote] That would be a daft call.[/quote]Agreed. But this is exactly the call made in the article.And seemingly promoted by opc.three to "best practices", if I understand him right.Hm, daft...:cool:Tue, 09 Apr 2013 15:55:42 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Sergiy (4/8/2013)[/b][hr][quote][b]quackhandle1975 (4/8/2013)[/b][hr]Odd reply, Sergiy. My comment was aimed at beginners and programmers who find themselves coding t-sql.[/quote]I reread your comment 5 times more - it does not say anything about "beginners and programmers who find themselves coding t-sql".Not a word. Not a hint.Intead - you put the blame on triggers and called to avoid them.Pretty stupid call.Sorry.I've seen .Net developers bringing dows 2 pretty powerful servers down by misusing remote calls in procedures.And exausting server tresources by using front-end formatting in views.What should we avoid here - remote calls, procedures, view?Because we won't avoid stupidity and incompetence - it's ours everything![/quote]Okay final comment on this matter. I hold my hand up and I should have mentioned that, where you can, try [b]NOT[/b] to use [b]CURSORS[/b] in [b]TRIGGERS[/b] if you [i]aren't aware of the consequences[/i]. I should have been clearer, I was not saying avoid triggers. That would be a daft call.I'm big enough to handle criticism with grace and politeness, Sergiy I suggest you try the same. Thank you opc.three for the support.Cheers[i][b]qh[/b][/i]Tue, 09 Apr 2013 05:13:11 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Sergiy (4/8/2013)[/b][hr][quote][b]quackhandle1975 (4/8/2013)[/b][hr]Odd reply, Sergiy. My comment was aimed at beginners and programmers who find themselves coding t-sql.[/quote]I reread your comment 5 times more - it does not say anything about "beginners and programmers who find themselves coding t-sql".Not a word. Not a hint.Intead - you put the blame on triggers and called to avoid them.Pretty stupid call.Sorry.I've seen .Net developers bringing dows 2 pretty powerful servers down by misusing remote calls in procedures.And exausting server tresources by using front-end formatting in views.What should we avoid here - remote calls, procedures, view?Because we won't avoid stupidity and incompetence - it's ours everything![/quote]How ridiculous. Why take everything to the absurd Sergiy? If you want to laugh in the face of best practices, go ahead, enjoy, but where is it your place to tear down those that are attempting to provide real help to those seeking assistance?Mon, 08 Apr 2013 22:39:57 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]quackhandle1975 (4/8/2013)[/b][hr]Odd reply, Sergiy. My comment was aimed at beginners and programmers who find themselves coding t-sql.[/quote]I reread your comment 5 times more - it does not say anything about "beginners and programmers who find themselves coding t-sql".Not a word. Not a hint.Intead - you put the blame on triggers and called to avoid them.Pretty stupid call.Sorry.I've seen .Net developers bringing dows 2 pretty powerful servers down by misusing remote calls in procedures.And exausting server tresources by using front-end formatting in views.What should we avoid here - remote calls, procedures, view?Because we won't avoid stupidity and incompetence - it's ours everything!Mon, 08 Apr 2013 16:05:19 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Sergiy (4/8/2013)[/b][hr][quote][b]quackhandle1975 (4/5/2013)[/b][hr]I have seen Cursors in triggers bring systems to a complete halt (I know as I had to debug it!). Best avoided IMHO.[/quote]I've seen 7 peple killed in a crash because American tourist were driving the van on the wrong side on a New Zealand road.Best not to let Americans to drive.If you find this comment stupid do not worry, I think of it the same way.Just keep in mind - it carries the same logic as your comment about triggers above.[/quote]Odd reply, Sergiy. My comment was aimed at beginners and programmers who find themselves coding t-sql.You may want to check out the following sites:[url=http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx]http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx[/url][i][b]qh[/b][/i]Mon, 08 Apr 2013 07:19:59 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxSergiy, you made my day lighter :-DMon, 08 Apr 2013 05:10:50 GMTRobert-378556http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]quackhandle1975 (4/5/2013)[/b][hr]I have seen Cursors in triggers bring systems to a complete halt (I know as I had to debug it!). Best avoided IMHO.[/quote]I've seen 7 peple killed in a crash because American tourist were driving the van on the wrong side on a New Zealand road.Best not to let Americans to drive.If you find this comment stupid do not worry, I think of it the same way.Just keep in mind - it carries the same logic as your comment about triggers above.Mon, 08 Apr 2013 04:41:27 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx↑↑↑↑↑↑↑↑ Wow, that looks nasty and no surprise it is Sage related (or how not to use SQL Server properly). :w00t:I have seen Cursors in triggers bring systems to a complete halt (I know as I had to debug it!). Best avoided IMHO.[b][i]qh[/i][/b]Fri, 05 Apr 2013 08:20:47 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]crazyEmu (2/21/2013)[/b][hr]Was confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.[/quote]Would not be surprised if this was a port from running on Oracle.Fri, 22 Feb 2013 07:01:12 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]crazyEmu (2/21/2013)[/b][hr]Was confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.It is supplied by a vendor (Sage \ SalesLogix) so dont blame me.[/quote]They could have avoided use of cursors by doing a simple MERGE between the INSERTED virtual table and the audit table. The key column on their table is called 'ENTITYID', so you make some pretty accurate assumptions about their T-SQL coding skills ...Fri, 22 Feb 2013 06:54:51 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxWas confronted by a trigger just yesterday, wonderful use of nested cursors. Just had to post it as an example.It is supplied by a vendor (Sage \ SalesLogix) so dont blame me.ALTER TRIGGER [sysdba].[ADDRESS_RECCHANGE] ON [sysdba].[ADDRESS]FOR INSERT, UPDATEASBEGIN DECLARE @recid char(12) DECLARE @conid char(12) DECLARE @reccount integer DECLARE tblCursor CURSOR FOR SELECT ADDRESSID FROM INSERTED OPEN tblCursor FETCH NEXT from tblCursor INTO @recid WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE tblAddr CURSOR FOR SELECT ENTITYID FROM INSERTED OPEN tblAddr FETCH NEXT from tblAddr INTO @conid WHILE (@@FETCH_STATUS = 0) BEGIN -- delete the old row for this record SELECT @reccount = count(1) FROM sysdba.SLXRECCHANGE WHERE (TABLENAME = 'CONTACT') and (ENTITYID = @conid) IF (@reccount > 0) BEGIN DELETE sysdba.SLXRECCHANGE WHERE (TABLENAME = 'CONTACT') and (ENTITYID = @conid) END -- insert new record into SLXRECCHANGE table INSERT INTO sysdba.SLXRECCHANGE (TABLENAME, ENTITYID, MODIFYDATE, CHANGETYPE) VALUES ('CONTACT', @conid, getutcdate(), 'C') FETCH NEXT from tblAddr INTO @conid END --while CLOSE tblAddr DEALLOCATE tblAddr FETCH NEXT from tblCursor INTO @recid END -- while CLOSE tblCursor DEALLOCATE tblCursorENDThu, 21 Feb 2013 23:39:26 GMTcrazyEmuhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxSpeaking as a Dev rather than a DBA I personally think triggers are the work of the devil. Mind you, that opinion's probably utterly unfair and mostly informed by the fact that I've worked on way too many systems where triggers were used to implement business logic. In terms of [i]business logic [/i]I want the database to be as dumb as a plank.I do believe, on the other hand, that the database should be responsible for retaining it's own integrity so if triggers are necessary for that (I'm not 100% convinced that they really are but there's always an exception) then fill your boots.On auditing, I think it's a grey area and really depends on the nature of the audit. If it's about detecting and tracking change at the database level then a trigger's probably the best way to go. If it's about producing a more "Businessy" audit that's likely to be consumed by users on a regular basis, then I think I'd probably argue for it to be implemented in a separate Business Logic Layer.All I'd really ask is this: if you're going to implement a trigger, then do so in such a way that I can do anything I could reasonably expect to as a dev (including directly updating data when the marketting department are breaking down in tears becasue they accidentally imported 1000 new customers under the wrong reference), in total ignorance of the trigger's existence, without accidentally bringing the world down around my ears. Preventing me from carrying out an erroneous action is fine. Allowing me to carry it out and then generating a bunch of new records and updating some aggregated values to a comletely garbage value isn't. I have alot of sympathy for the "you should have read the docs" argument but I can only retain so much knowledge and I've often found myself in situations where the time to check the documentation is a luxury that is unavailable to me for reasons that are out of my control. I'm not dumb or lazy... but it's probably a good idea to assume that I am.Wed, 20 Feb 2013 05:37:16 GMTFunkyDexterhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]My argument was based on the use of triggers to implement business logic and to do the sort of data integrity checking that should be enforced by keys and constraints. I should have made that a bitmore explicit.Cheers,Tony.[/quote]That was an inappropriate use of triggers from the very beginning.It was a "legacy" from the day triggers were introduced.The closest point where triggers should be coming to constraints is preventing run-time errors when a constraint is violated by some change in data and divert the data set causing such a violation to some "data revision" process.Tue, 19 Feb 2013 18:16:16 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]jfogel (2/19/2013)[/b][hr]I get that but my point is that sometimes you don't want this and if a person is performing an action without knowing or forgetting there is a trigger they run the risk of triggering (pun intended) other processes they had no intention of kicking off such as 10,000 emails were sent because they inserted that many records.[/quote]That's exactly the point of having a trigger!If the logic of the system (or audit spec) require report every addition to the data via email then - sorry - you should obey.And if a bugger is trying to sneak around and bypass some required steps - triggers are the best tool to prevent such a breach.But if you need to email only new data added via UI or some other specific interface - then emailing call must be implemented in that interface, not in the trigger on the base table.It again comes to incorrect usage, not to any kind of problem with triggers.Tue, 19 Feb 2013 18:04:59 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Lynn Pettis (2/19/2013)[/b][hr][quote]As for horses, you did mention them first.[/quote]I guess this indicates that you can lead a horse to water, but you cannot teach him to like triggers. Although some horses did like Trigger.Tue, 19 Feb 2013 14:34:20 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]jfogel (2/19/2013)[/b][hr][quote][b]Lynn Pettis (2/19/2013)[/b][hr]If documentation exists and you fail to read it, you have only yourself to blame. Just pointing out one of the possible reasons you may not know why a trigger exists.A properly designed database, with proper documentation will provide that information if one avails themselves to read it.Oh, someone once told me that yes, you can lead a horse to water and make them drink. Person who told me this happens to own horses.[/quote]You keep using the word 'you' as if it were I that was unaware of the trigger. I assure you this isn't the case. In fact, the person who caused the problem was also the very same person who brought the initial issue to use that prompted the creation of the trigger to begin with. They were very much aware of its existence, how and why it worked, etc. They just plain forgot and the worst part is that this happened not years after the trigger was implemented but something like two days later. So, please, tone down the 'you' and perhaps replace it with 'they'.As to the horses.. I don't care.[/quote]As for horses, you did mention them first.Tue, 19 Feb 2013 14:25:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxI apologize if you are taking the use of the word "you" personally. It was meant in a general third person manner.Tue, 19 Feb 2013 14:24:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Lynn Pettis (2/19/2013)[/b][hr]If documentation exists and you fail to read it, you have only yourself to blame. Just pointing out one of the possible reasons you may not know why a trigger exists.A properly designed database, with proper documentation will provide that information if one avails themselves to read it.Oh, someone once told me that yes, you can lead a horse to water and make them drink. Person who told me this happens to own horses.[/quote]You keep using the word 'you' as if it were I that was unaware of the trigger. I assure you this isn't the case. In fact, the person who caused the problem was also the very same person who brought the initial issue to use that prompted the creation of the trigger to begin with. They were very much aware of its existence, how and why it worked, etc. They just plain forgot and the worst part is that this happened not years after the trigger was implemented but something like two days later. So, please, tone down the 'you' and perhaps replace it with 'they'.As to the horses.. I don't care.Tue, 19 Feb 2013 14:19:07 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Lynn Pettis (2/19/2013)[/b][hr]If you don't know the trigger is there, it is probably due to poor, missing, or incomplete application/database documentation.[/quote]Fortunately SQL Server is self documenting, at least in terms of cataloging what objects are in the database, their relationsip, usage stats, and etc. I occasionally find myself in situations where I must familiarize myself with an undocumented legacy database. Fortunately, the databases are the end of their lifecycle and my job is to either develope a replacement or at least ETL the data into a new database before it retires.I have a collection of scripts that query configuration settings, jobs, triggers, referential constraints, object dependencies, most frequently read/written objects, obsolete objects (never read/written), etc. and then prints out a report. This will more reliably tell me what going on than what I can get out of the last guy who touched the database.Tue, 19 Feb 2013 14:02:15 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxIf documentation exists and you fail to read it, you have only yourself to blame. Just pointing out one of the possible reasons you may not know why a trigger exists.A properly designed database, with proper documentation will provide that information if one avails themselves to read it.Oh, someone once told me that yes, you can lead a horse to water and make them drink. Person who told me this happens to own horses.Tue, 19 Feb 2013 13:12:57 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxIt could be a text book and people will refuse to consult it. Apparently you've only worked with people who read everything before doing anything. I and I suspect most of us here wish we could claim the same but can't. Why you insist that the cause of these things are always due to bad docs is lost on me. Its the whole leading a horse to water thing. Cant make it drink and if people refuse to read then usually many pay a price.Tue, 19 Feb 2013 13:06:29 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxWell, if it is poor, missing, or incomplete there really isn't much reason to read it, is there. :w00t:Tue, 19 Feb 2013 12:56:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxYou assume people would bother to read such things. We all know everyone and every level reads the docs :)Tue, 19 Feb 2013 12:47:28 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxIf you don't know the trigger is there, it is probably due to poor, missing, or incomplete application/database documentation.Tue, 19 Feb 2013 12:41:56 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Eric M Russell (2/19/2013)[/b] I think there is more to be gained by avoiding them than advocating them.[/quote]Complete agreement here. I think I have written only a few in my career. For the most part i have done everything I could to avoid them and have been successful. There are better ways of doing most things.Tue, 19 Feb 2013 12:39:53 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxI get that but my point is that sometimes you don't want this and if a person is performing an action without knowing or forgetting there is a trigger they run the risk of triggering (pun intended) other processes they had no intention of kicking off such as 10,000 emails were sent because they inserted that many records. Of course things like that are rare but once is enough. Yes, triggers can be useful but in my experience they are trouble when others don't realize they are there.Tue, 19 Feb 2013 12:29:30 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]jfogel (2/19/2013)[/b][hr]... because far too often they are doing things others are not aware of ...[/quote]But that is the whole point of a trigger!To implement some feature that works transparent and automatic, on top of and without complicating, all those other things going on! For this you should love triggers that do not have unrelated side-effects. I do agree however to keep usage of triggers to what is absolutely nececary. If a feature can be done declarative or in a procedure where said transparancy is no requirement, go for that instead!Unfortunatly since 2005 no trigger is guaranteed transparent due to the introduced output clause (bad implementation). Anyone using triggers to audit, log or debug using triggers for an application they do not know in full, might break the application doing so unknowingly!The horror for me is to see everything (RI and interaction), wrapped in stored procedures as then the database is used as dumb storage (in an SQL way). With the same inflexibility and torrents of code that come with stuffing everything in a middle tier. Need to do some reporting...well, add yet anoter procedure. Need another filter parameter for some listview,...complicate things further in a procedure or add another specialised one. It is the path that never ends to generate more complexity and never leverages the concept of the relational database.Tue, 19 Feb 2013 11:50:15 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxI despise triggers because far too often they are doing things others are not aware of and they will spend too much time trying to find the cause only to eventually get to the root cause of the problem and that is the trigger. Before I will create a trigger for a business process a convincing case mast be made as to why something like that should happen and 99.9% of the reasons do not make the cut. The only time in recent memory where I found a trigger useful was trying to pin down how a critical value for a single record was changing in a particular table. The users denied it, we couldn't find a cause in the application code so I created a trigger to detect if that record changed and if so send an email alert to the principles involved. It worked great and the best part is that we dropped it when done.Tue, 19 Feb 2013 11:09:38 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Miles Neale (2/19/2013)[/b][hr][quote][b]Eric M Russell (2/19/2013)[/b]Those Entity Framework and heterogeneous architecture guys should stick to the .NET conferences where the audience doesn't know any better.[/quote]While I agree that there can be a lot of cleanup in some databases, and that some .net developers using the Entity Framework create systems and databases that are challenging, that does not justify the attitude in the above statement. Such is the attitude that hampers the industry, can kill teamwork and possibly a project, and can make ones career difficult.[/quote]I apologize for the tone. It is technically possible to architect Entity Framework or heterogeneous database applications that perform well and are well maintained. I'm open to the possibility. However, I think there is more to be gained by avoiding them than advocating them.Tue, 19 Feb 2013 10:53:54 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Eric M Russell (2/19/2013)[/b]Those Entity Framework and heterogeneous architecture guys should stick to the .NET conferences where the audience doesn't know any better.[/quote]While I agree that there can be a lot of cleanup in some databases, and that some .net developers using the Entity Framework create systems and databases that are challenging, that does not justify the attitude in the above statement. Such is the attitude that hampers the industry, can kill teamwork and possibly a project, and can make ones career difficult.Tue, 19 Feb 2013 10:29:11 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote]Last year at PASS I heard one person proudly proclaim that we "like our databases dumb!" He, like many others, wanted to avoid putting any logic in the database in favour of placing it all in a middle-tier layer. This, they argue, leads to a more scalable and portable architecture.[/quote]LOL! I spend a lot of time cleaning up "dumb" databases where the middle tier or ETL is assumed to contain all the logic needed for data quality and referential integrity. Those Entity Framework and heterogeneous architecture guys should stick to the .NET conferences where the audience doesn't know any better.However, I do agree that triggers are most often misused, especially in scenarios where a check constraint would have been a better choice.Tue, 19 Feb 2013 10:17:11 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]GilaMonster (11/8/2008)[/b][hr]Triggers have uses, but I'm not sure that business logic is a good one.For me, auditing is the main use. In 2008 Change data capture and change data tracking can also be used for that, but not everyone is using 2008. Other thing that they can be used for (especially the DDL triggers) is rolling back unauthorised changes, though if you have that kind of changes been made either security is lax or the DBA is careless.[/quote]I agree Gail, I have often found that people are using triggers for things which they probably shouldn't be using them for… Plus, it's too easy for most people to just plain forget or overlook that they are even there. Even though you can find them rather easily....:-DTue, 19 Feb 2013 09:19:09 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxA database should be stitched up tight as a drum with constraints that ensure referential integrity. Otherwise they should be dumb as a stone. And you will put a trigger on my database design over my cold, dead body.Tue, 19 Feb 2013 08:59:40 GMTGeorgeCopelandhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Bert Scalzo (11/8/2008)[/b][hr]Triggers do still have one legitimate use that constraints cannot handle. If the logical design includes super and sub-types, and the physical implementation is the one table per child choice - then the only way to guarantee that the primary key remains unique across the tables is via a trigger. Constraints only perform their function within the context of a single table. So a trigger PK check across tables is the only answer.[/quote]Sorry, but that use of triggers is also a legacy one. If you have a requirement for unique keys across tables, then use GUIDs.Tue, 19 Feb 2013 08:57:41 GMTGeorgeCopelandhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote]1) If you insist on "begin" and "end" as separate columns, what about a function based table constraint, where the function checks for overlapping ranges?[/quote]Because function based table constraints are not set orientated and triggers are. I think you will run into some concurrency problems with check constraints using functions.Tue, 19 Feb 2013 08:50:53 GMTwilliam.sissonhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxI use triggers to make updatable views, which buffer downstream developers from complex schema. Is there a way better than through triggers to provide this functionality?Tue, 19 Feb 2013 08:47:32 GMTRSheahanhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]william.sisson (2/19/2013)[/b][hr]For example for hotel room bookings you need to ensure that the same room cannot be booked for overlapping time periods. You need a constraint to do this that cannot be enforced using referential integrity or check constraints.[/quote]1) If you insist on "begin" and "end" as separate columns, what about a function based table constraint, where the function checks for overlapping ranges?2) Alternately, you move on to the "DateOfChange" technique where you have only the "Begin" type column, and you record "Customer A begin" and then either "Customer B begin" or "Empty begin" in another record. LAG/LEAD made this much easier in 2012, but it can still be done without much trouble in earlier versions.Tue, 19 Feb 2013 08:13:54 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic599262-263-1.aspx[quote][b]Carlo Romagnano (2/19/2013)[/b][hr]I mainly use triggers to modify the behavior of third-party db and to alert users by e-mail that some value has been set. e.g. changing terms of payment is permitted, but users should know it.[/quote]Since 2005 you cannot do this anymore, unless you know for 100% sure the application never will use the output clause in a conflicing manner! See my previous post as to what this issue exactly is.Tue, 19 Feb 2013 04:33:16 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic599262-263-1.aspxTriggers are downright unavoidable for some comon problems. Unless you wrap all database actions in an interface represented by procedures (DB or otherwise) and consider the DB just dumb storage without full referential integrity of course. But then you cannot fully leverage the flexibility a database offers.Where I used triggers in the recent past:[b]Table A has two foreign key relations to table B, both you want to have “set null” or “set default” cascading behavior.[/b]With DRI you cannot, you have to choose one foreign key relation, SQL server won’t let you have two.There is no nice way around this, but to introduce an instead-of trigger and do everything manually.With a trigger all normal database manipulation actions are still working as before, which compared to wrapping access up in stored procedures is superior.[i]Instead of insert triggers have a slight but dumb issue in that for an insert to happen you have to specify each and every column...even the nullable ones and ones with defaults![/i][b]Table A contains data that is entered once a month, and Table B has closely related data that has to be entered daily.[/b]Exports and imports work on the day resolution and record modification dates and record existence have to be kept in synch.Entering a data in a month record needs to introduce or update all the related day records too, or else the export can’t work.The reverse is also needed, a day record insert/update must create a create/update the corresponding month record as well. DRI cannot do correlated inserts, nor correlated updates, these have to be programmed explicitly in either trigger or procedure. The issue I have with procedures in general is that you dispense with the normal abilities of the database and have to route all access through explicitly coded procedures. If you want something simple, odds are it is not in one of the procedures and you have to code even more! It’s like going back to a stone age of development with all the extra complexities![b]Unfortunately the existence of and need for triggers seem to be forgotten by the SQL developer team, these last 7 years![/b] A case in point for this argument is that the output clause in an insert/update cannot be streamed back to the client directly if a trigger exists on the table being modified. You can use the output clause to stream to a table and then do a select on that table afterwards however. But then you lost all the elegancy of the construct. There are more common shortcomings with the output clause, but that is for another day.Another case is cascading foreign constrains conflicting with instead-of triggers.[b]This lack of transparent functioning of these new features with the absolutely still needed triggers is making use of these new features a serious problem as you cannot use any without knowing in full advance what is used now and needed into the future.[/b]Adding s trigger to solve a problem for an application that sometimes uses the output clause that you don’t know about, will break the application even when you did nothing wrong and your added code is working properly!The reverse is also very true, making these following features [b]tainted[/b] and generally unavailable if you did not design and implement database and application from the ground up yourself:• DRI (cascading behavior part)• Triggers• Output clause on insert/update that steams not to a tableIf you abstain from using triggers, then you cannot implement RI properly, even for some very common cases/needs!If you ask me, this is a big mess and Microsoft needs to get their functional design in order and real quick too!They have been introducing broken features for 3 releases now, with no end in sight! Given the slow pace of change in database feature usage, they messed up their product for years to come. The only saving grace seems to be the very slow adoption of new features in the field and the willingness of DBAs to spend days, even weeks, on kludge workarounds for even the simplest of problems.My current MO is to avoid using both triggers and procedures as much as possible. Where I work this is possible because we control both the applications and the database from the ground up, others are likely much more constraint! Still, this sometimes means I have to lessen RI more then i wish for. The upside is I do not have to feel so constraint in using new SQL constructs.I hate lessening on RI and this is the reason I judge hard. Somone as Microsoft responsible for QA really needs to wake up hard!Tue, 19 Feb 2013 04:19:29 GMTpeter-757102