June 22, 2016 at 8:33 am
BWFC (6/22/2016)
Honestly I can't wait until this week is over. My Facebook feed is full of spiteful immature comments rather than rational debate. Most of these comments having no hard facts behind them and relying merely on fear-mongering and heavily biased media outlets. According to remain voters, we'll lose all our human rights if we leave. According to leave voters, we'll lose all our jobs if we stay. (None of these backed up by evidence.)
I saw a post yesterday where someone had claimed we must remain in the EU because the leader of ISIS wants us out. The whole thing would be funny if it wasn't so sad.
Hear hear. Although, I'm expecting to have to endure a week or so of the losing side complaining that they were robbed and wailing and gnashing their teeth about the future.
Oh yes. And the endless online petitions demanding that we have a revote.
June 22, 2016 at 8:38 am
Brandie Tarvin (6/22/2016)
BWFC (6/22/2016)
Lynn Pettis (6/21/2016)
Brandie Tarvin (6/21/2016)
Lynn Pettis (6/20/2016)
Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.Does anyone else see a problem with the following code?
Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update
AS
Begin
Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)
Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN
From inserted i;
End
I was going to snigger and snort and gfaw over this one, but after reading the following posts I see the issue has already been pointed out.
This is what happens when DBA wanna bees do knee jerk reactions to solve data issues without thinking or talking to the only DBA in the company. Trust me, yesterday was not a good day for me, I actually talked out loud about maybe looking for a new job. I got talked down from that ledge.
Could somebody explain what's wrong with it please? I know nothing about triggers. I'm the reporting guy so they're not the kind of thing I come across. I've never had any call to use one but I'd be in grave danger of falling into whatever traps lie within that it if I ever needed a trigger.
"Instead Of Insert, Update" means that when someone tries to insert data into or update data in table dbo.OperationsSIGACTTable, the trigger will rollback the insert / update and then log the history of the attempted insert somewhere. This is a good idea in practice. The OP, however, has chosen the exact same table in which to insert the attempted data insert/update. Which will yet again trigger the trigger if recursive triggers are allowed. Think of it as an infinite loop kind of process.
If you have a trigger preventing inserts that is then trying to do its own insert, it's a self-defeating process. And updates are just as bad.
The "inserted" table the SQL is pulling from is a non-documented "invisible" table that only exists for the length of the transaction. Everything that gets inserted / updated finds itself in the inserted table until the next update / insert happens elsewhere. There is also a "deleted" table that does the same thing for deletes and updates (because an update is actually a delete followed by an insert that we never see).
Does that help?
EDIT: Here is a TechNet article on SQL 2008 R2 INSTEAD OF triggers. Nothing's really changed in SQL 2012 that I can tell.
Actually, Brandie you are not quite right here.
Fast background. There are two types of triggers that can be placed on tables, AFTER triggers (the types most DBAs know about) and INSTEAD OF triggers (a little less known). The difference between them is this, AFTER triggers fire after the event on the table they are defined for, either INSERT, UPDATE, and/or DELETE; and INSTEAD OF triggers fire BEFORE the event on the table (or view).
INSTEAD OF triggers allow you to intercept the INSERT, UPDATE, or DELETE on a table or view before it actual is done allowing you to do additional processing. For instance, if a batch insert into a table contains duplicate data that would fail on a primary key or unique constraint when a normal insert is done (no INSTEAD OF trigger on the table); an INSTEAD OF trigger can be defined on INSERT to allow code to be written in the trigger that would allow a partial insert to be completed on the table and the rows of data that would have failed the insert to be inserted into a separate table for review and/or correction.
In the case I posted what is happening is that there is an issue with special characters being inserted into our database that aren't handled properly. The INSTEAD OF trigger intercepts the insert into the table and performs its own insert into the table running the scalar function on the specified column and passing all the other columns untouched to the table. The problem with the INSTEAD OF trigger is that it is defined for both INSERT and UPDATE. If an update statement is run on the table, the INSTEAD OF trigger is fired and instead of doing an update in the trigger, it attempts to do an insert.
There is also a problem with the scalar function as it uses a loop and it only keeps the lower ASCII characters between char(32) and char(127), and the input parameter is defined as varchar(max) when many of our character columns are defined as nvarchar(?) to handle unidode data. This means Unicode data could be permanently mangled and any high ascii characters char(128) to char(255) will be lost.
If anyone else would like to weigh in on this or expand the explanation, please do.
June 22, 2016 at 8:39 am
Grant Fritchey (6/22/2016)
Being a different kind of science fiction fan, one of my horrors is the world government that will tell us all what to do, how to do it, when to do it, why to do it, where to do it. Followed by a long list of items that are proscribed, prohibited and just generally frowned upon (with the full weight of that world government, even frowns hurt).
The only thing you left out is that they tell us what to think. 😉
June 22, 2016 at 8:42 am
Lynn Pettis (6/22/2016)
Actually, Brandie you are not quite right here.
I knew as soon as I hit POST that I said something wrong, but wasn't able to get back to it immediately to figure out what I misstated. Thanks for the correction, Lynn.
June 22, 2016 at 8:45 am
A little more on INSTEAD OF triggers.
They can be declared on views. They can be used to make non-updateable views updateable because you can handle the updates (in this case inserts/updates and/or deletes) in the INSTEAD OF triggers. I suggest anyone wanting to know more read up on VIEWs.
June 22, 2016 at 8:48 am
Brandie Tarvin (6/22/2016)
Lynn Pettis (6/22/2016)
Actually, Brandie you are not quite right here.I knew as soon as I hit POST that I said something wrong, but wasn't able to get back to it immediately to figure out what I misstated. Thanks for the correction, Lynn.
Not a problem, you took a chance, which is more than what some do. That is NOT a knock on any of the Threadizians. I have worked with people that think they know things but won't even venture an explanation in a public forum.
June 22, 2016 at 8:53 am
As to the Brexit...
I thought the most interesting comment on it all that I saw was that there's much more reason for the UK to have common cause with Australia than there is for the UK to have common cause with the majority of the EU member states.
That may be the case Grant and I more or less agree, see my remark about not voting to join the EU. There is a world of difference between deciding not to get married and going through a messy divorce though.
For better or worse we're part of the EU. There is just far too much uncertainty about what will happen if we leave to think about risking it. The Leave crowd have been going on and on about how they'd like it to be but they've not once said, 'and this is how we'll do it'. They've talked at length about making our own deals with trading partners but they seem to have overlooked how they'll get a better deal than the one we've currently got. Their argument is nothing better than 'we just will.'
Tomorrow, a group of people who voted for Steve Brookstein to win X-Factor, a dog act to win Britain's Got Talent and Boaty McBoatface are going to make the biggest decision to affect my country in my lifetime. I'm f@*king terrified.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 22, 2016 at 8:54 am
Ed Wagner (6/22/2016)
Grant Fritchey (6/22/2016)
Being a different kind of science fiction fan, one of my horrors is the world government that will tell us all what to do, how to do it, when to do it, why to do it, where to do it. Followed by a long list of items that are proscribed, prohibited and just generally frowned upon (with the full weight of that world government, even frowns hurt).The only thing you left out is that they tell us what to think. 😉
Remembering an old song...
"Everything you think, do, and say... will be in the pill that you took today!"
Heh... reminds me of supposed "Best Practices". 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2016 at 9:00 am
BWFC (6/22/2016)
As to the Brexit...
I thought the most interesting comment on it all that I saw was that there's much more reason for the UK to have common cause with Australia than there is for the UK to have common cause with the majority of the EU member states.
...
Tomorrow, a group of people who voted for Steve Brookstein to win X-Factor, a dog act to win Britain's Got Talent and Boaty McBoatface are going to make the biggest decision to affect my country in my lifetime. I'm f@*king terrified.
Just making sure...
No one gave the secret of DBCC TIMEWARP() to these people, right? I would hate to see how those types would misuse such a powerful function.
June 22, 2016 at 9:00 am
I'm f@*king terrified.
Me too, and ashamed of the xenophobes.
Back to SQL Server, I work for a company that has a lot of trading systems. We are expecting meltdown tomorrow & Friday :-D.
June 22, 2016 at 9:02 am
/me leans back, marveling at what happens when you toss a crystal into a supersaturated solution...
Sounds like the next couple weeks / months are going to be interesting, regardless of the outcome of the vote.
Also sounds like the same things are true all over...
* Politicians aren't worth their weight in garbage
* Voters (in general) are id10ts and will vote for whoever yells loudest / promises them what they think they want
* Water is wet
:hehe:
June 22, 2016 at 9:11 am
Lynn Pettis (6/22/2016)
A little more on INSTEAD OF triggers.They can be declared on views. They can be used to make non-updateable views updateable because you can handle the updates (in this case inserts/updates and/or deletes) in the INSTEAD OF triggers. I suggest anyone wanting to know more read up on VIEWs.
Or write a series. I'd like to have a few pieces on how different triggers work.
June 22, 2016 at 9:12 am
Beatrix Kiddo (6/22/2016)
I'm f@*king terrified.
Me too, and ashamed of the xenophobes.
Back to SQL Server, I work for a company that has a lot of trading systems. We are expecting meltdown tomorrow & Friday :-D.
Heh. The SO works for Deutsche Bank. Well, he does today. Next week he has a new job, tomorrow & Friday are vacation days. So he gets to miss that meltdown. I've heard (from other sources) comments about change freezes and such at all major banks and trading institutions to insure that said meltdown doesn't get worse by code put into Production by some foolish, aspiring developer.
Which is very very wise IMHO.
June 22, 2016 at 9:16 am
BWFC (6/22/2016)
As to the Brexit...
I thought the most interesting comment on it all that I saw was that there's much more reason for the UK to have common cause with Australia than there is for the UK to have common cause with the majority of the EU member states.
That may be the case Grant and I more or less agree, see my remark about not voting to join the EU. There is a world of difference between deciding not to get married and going through a messy divorce though.
For better or worse we're part of the EU. There is just far too much uncertainty about what will happen if we leave to think about risking it. The Leave crowd have been going on and on about how they'd like it to be but they've not once said, 'and this is how we'll do it'. They've talked at length about making our own deals with trading partners but they seem to have overlooked how they'll get a better deal than the one we've currently got. Their argument is nothing better than 'we just will.'
Tomorrow, a group of people who voted for Steve Brookstein to win X-Factor, a dog act to win Britain's Got Talent and Boaty McBoatface are going to make the biggest decision to affect my country in my lifetime. I'm f@*king terrified.
I don't know enough about what the issues are or benefits/problems. However, this seems like a reactionary move, which I never think is good for any large organization. Things that seem easier (marriage/divorce) in small areas often don't scale. We know that in this business.
I know sometimes marriage or divorce is hard and painful, but that's small compared to a country. At least Britain didn't change money, but lots of systems change. Maybe that's good for Britain, lots of work, new things, but maybe it's a waste. I lean to the latter. I HATE companies changing logos, slogans, visions, brands, etc. without real internal change.
In terms of the world government, makes sense if we find aliens. Otherwise, I'm only for a large world government if it's smaller and less intrusive than current governments. Set wide guidelines, wide bumpers for how we conduct business.
The problem I see is that economically we're not close as countries, and not all cultures want openness. Probably few. Far, far too many organizations count on some protectionism from a country, so the "get there from here" problem is huge.
June 22, 2016 at 9:21 am
Beatrix Kiddo (6/22/2016)
I'm f@*king terrified.
Me too, and ashamed of the xenophobes.
Back to SQL Server, I work for a company that has a lot of trading systems. We are expecting meltdown tomorrow & Friday :-D.
Yeah me too, and we have a ton of different nationalities in this office.
No finance company will be unaffected either way. Brexit would make for some tricky interfacing issues for us and could directly impact trade.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 54,676 through 54,690 (of 66,688 total)
You must be logged in to reply to this topic. Login to reply