To be honest, the transaction should be affecting both tables without relying on triggers at all.
I suspect I'll have to tell him to unacceptable my answer because I removed the code. I still feel I've answered the actual question but it would be more complete with code I just don't feel like testing.
1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?
This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement th...
I upgraded my MSSQL server from 2008 to 2014 and I can no longer get online. All web sites say waiting on website to load. Does anyone know what happens during this upgrade that might have caused this?
@PaulWhite Yes, I know! I was only curious if comparing you to (or mistaking you for) a Canadian was better than an Australian. But as the question is now deleted, it doesn't really matter. :)
@PaulWhite Nice. A couple of questions if I may? The check for TRIGGER_NESTLEVEL is that necessary or perhaps a case of "better safe than sorry" because of unknowns in other places.
Question 2: I read your blog post on Temporary Tables in Stored Procedures last week and it was extreamly helpful in figuring out a performence issue at a customer site and in how to fix it, thank you for that. In this trigger you added the recompile but you did not do the update statistics on the temp table. What would be the reason for that?
Perhaps because joining in on the primary key and recompile is enough for the optimizer to see the actual rowcount in the temp table. (Typing out loud as I think here...)
@MikaelEriksson Better safe than sorry/couldn't be bothered to think deeply about it. It's presence might encourage people to read up on it </optimist>
@MikaelEriksson I did originally have an explicit update stats, but as it is, there's no dependency on stats distribution for a plan choice. Raw cardinality is enough.
I had a FORCESEEK at one stage too, to avoid range locks, but in the end I decided against it. So many potential issues for such a "simple" requirement.
Writing a trigger solution that is robust and efficient under high concurrency is a great exercise, even for those of us that have been doing SQL for a long time. I still find it tricky.
@PaulWhite How would you tackle the issue if not with a trigger? I believe I would end up with stored procedure running in serializable and giving up on concurrency.
@PaulWhite Ok, thanks. Pro for constraint solution is TotalQty >= 0 check constraint that needs to be implemented as a business rule in the indexed view. Indexed view should be a lot faster to retrieve the current balance over multiple accounts and looks a lot cleaner.
I am trying to import a given dump file using oracle imp utility.
However; I am facing the following issues.
C:\Users\AD>imp DB_USER/*****@dev1 file=C:\test\db42312.dmp log=C:\test\import2.log
Import: Release 11.2.0.1.0 - Production on Thu Apr 10 13:37:47 2014
Copyright (c) 1982, 2009, Oracle ...
There seems to be a shift away from the way we were taught in the past when using column aliases. It is now recommended to avoid using the "AS" and switch placement using "=" when using column aliases in your code to allow easier reading and troubleshooting.
@FreshPhilOfSO The only time I find them handy is when I am exporting a result set, it lets me put spaces into something before I copy/paste into excel
@Zane at this point consistency
I dealt with a number of coding standards at my last job that I found onerous, but had to continue them because we had such a large codebase with them already in place
Hmm.. on a serious note if one can read and write the answer to a question in less than 60 seconds then it'd usually signal a problem with either the question or the answer. Chances would be that the question is a FAQ in which case it better be closed. The answer is also likely to be a low-quality post unless a link-only answer in which case it could be improved by the readers. — devnull6 hours ago
What is the SQL SERVER column data type for the same column?
It seems you have used the nchar column for the SQL SERVER table column.
Since the destination oracle column,Unitprice is numeric it does not allow to inser any character type data.Ensure any character data in the incoming source column
Well i am trying to understand what exactly is BI Cube. Online tutorials are technology based. I want to know whether it's something in Database like a table or It's a technology thing ? Can anyone explain me what it is in the context of SQL Server?
I've been using SO for a few years, and initially most questions you asked got a positive reception: happy comments and answers, people that tried to help you out regardless of the way you worded it. Of late the tendency is to simply close or put on hold most questions, or downvote them, or make ...
@Zane I think I had a similar question about a cube not long ago. I wanted to know whether it was just another, specialised, instance of SQL Server or a completely different kind of server. In particular, whether the cube data (facts and dimensions) were stored in a SQL Server database or in a different kind of storage.
Hi I have a table that looks like this. Its for a review of restaurant facilities
RestName Quality Cost Service RangeOfDishes
KFc 4 3 4 5
MCDonalds 3 4 4 2
KFc 4 3 4 5
MCDonalds 3 2 ...
I used to get "upset" (though that is too strong a term) when I got downvoted without comment. If my answer isn't good enough then I'd like to know why. Not only does it improve the answer for the OP but it improves my knowledge too.
Where the down-vote has been explained I've found it useful & ...
@Lamak I agree with that - or perhaps match the rep of your highest site in the network. No reason you shouldn't get the same privs there as the place where you've earned them directly. Don't think it should be aggregate though - someone could join 100 sites and get 10000 network rep just through association bonus.
I don't think any of the meta sites should be reputation-based - it shouldn't cost anything to down-vote bad answers, and fear of down-votes shouldn't make people avoid participating in the discussion.
@AaronBertrand You are right about getting at max the most privileges that you have on a site instead of giving you privileges that you don't have in any site
Hi I have a table that looks like this. Its for a review of restaurant facilities
RestName Quality Cost Service RangeOfDishes
KFc 4 3 4 5
MCDonalds 3 4 4 2
KFc 4 3 4 5
MCDonalds 3 2 ...
@JNK but why is that only a valuable indicator on over-arching meta.se, but not on any of the individual site metas? I could have 100K on this site and never visit meta at all, meta shows my rep as 100K too.
@JNK I should've explained better. I think that meta.SE should make your rep some calculation of your network rep (maybe the max rep on some site or similar, but as @AaronBertrand said, not the SUM). And if you think that meta.SE rep should be based on votes there, then the privileges at least shouldn't be tied to that rep directly, but on privileges that you have already earned on some SE site
@JNK I don't really agree. We talk about the same types of policies and topics, some of them just happen to be specific to SO only (e.g. anything volume- or Skeet-related).
@bluefeet we went through this a year or two ago, where everyone was always asking other site mods if they wanted a question and it wasted a ton of time (esp. for SO mods). Since they added rejection it doesn't make sense, esp. for you since you know DBA.SE so well
@bluefeet Just remember, that poop highway can flow both ways ;)
I'm still not sure I understand why this site is reputation-based. I think that all this does is make people afraid to participate in conversations.
All of the per-site metas are not reputation-based - the privileges you have there match what you've earned on their main site. Does the amount of participation you've had exclusively in the per-site meta affect your ability to jump into a conversation about issues on a site where you have participated highly? No. Should you be afraid of losing reputation for sharing an unpopular opinion there and getting down-voted into oblivion? No, and I th…
@Zane you could, if it's better than the one posted, but that one seems to answer it correctly
@AaronBertrand I agree with how you wrote it, and it's many times better than what I could wrote
I would preemptively try to reason with people saying that the rep should be earned there though. And maybe make the case for only privileges from your most rep site be transferred to meta
I'm facing a problem in a package to import some data from a MySQL table to Oracle table and MS SQL Server table. It works well from MySQL to SQL Server, however I get an error when I want to import to Oracle.
The table I want to import contains an attribute (unitPrice) of data type DT_R8.
T...
You have to refer to that in your having clause
SELECT
grupo,
solicitante,
sum(valuacion) as valuacion,
fecha,
count(*) as cant,
sum(aprobado) as ap,
sum(goe) as goe
FROM
presupuestos
WHERE
grupo>0 AND
elim=0 AND
sup=0 AND
goe=1 AND
aprobado=0...
Hi I have the following query to select records from database it working file also
select Barcode,systemqty from items where barcode in
(select itibar from itrans group by itibar having sum(itiqty) <> systemqty)
and brand='BRO'
But I need to select another field from itrans table like thi...
Connecting to a report server running Win 2k8 R2 with SQL 2012. The dataset is coming from a linux box running MySQL.
I can create a report in Report Builder fine. All the data pulls from MySQL without an issue. However, when I go to run the report I get an error:
For more information about thi...
> In the British and Commonwealth English, the "rump steak" is commonly called the "sirloin" in American English. On the other hand, British "sirloin" is called "porterhouse" by Americans.
So, this guy sees that if he filters normally gets good results, but then still asks why shouldn't he convert his dates to varchar and then do the filtering
I'm writing a query that will generates a weekly report
The query looks like this:
DECLARE @rundate DATETIME
DECLARE @oneweekago AS DATETIME
SELECT @rundate = getdate() - 2
SELECT @oneweekago = DATEADD(week, -1,@rundate)
SELECT d.date, t2.column1, t1.column2, t1.column3
FROM table1 t1 WITH (NO...
@Lamak it's just baffling to me that these enterprise solutions can't handle the concept of formatting dates correctly on the front end so they just convert both to a different data type to compare on the back end. it's rage-inducing. great. now i need a greyhound
He's been really good on the forecheck and from what I've seen hasn't been a huge turd. However you're right he won't be terribly missed for the most part the type of players who take these sort of suspension aren't normally your impact players.
@JNK i can see that. at that time, invest in a machine with a lower (but faster) core count and a superabundance of ram ... you'll cut licensing costs too :)
How can I create a password, which when directly hashed (without any salt) with md5 will return a string containing the 8 characters "SALT ME!". The hope is that a naive developer browsing through his user database will see the "hash", realize the insecurity of his application, and eventually mak...
I checked it THOROUGHLY, but I can't find any mistakes.
I even checked with my similar query and everything's about the same...
USE mydb;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id int auto_increment,
username varchar(255),
password varchar(255),
primary ...
I am interested in programmatically selecting the most recent .XLS file in a folder and (using ETL) import the data from that file into a table. The files all have a "root" name plus a datestamp in their filename, so it isn't just a matter of pointing to a filespec in a folder.
Thanks!
@Daи I mentally treat anything I've already joined up as one 'table', then whatever I'm adding on is the other half of the Venn diagram. Does that make any sense?