October 23, 2007 at 9:13 am
I have an update trigger that inserts into a second table if a date field is populated.
When I populate that field to test the trigger, the second table receives 2 records
Here is my trigger. Any ideas?
CREATE TRIGGER trg_#1 ON dbo.table1
FOR UPDATE
AS
IF ((SELECT DISTINCT dtmDateApproved FROM INSERTED) IS NOT NULL)
BEGIN
INSERT INTO tbl_table2(table1ID)
SELECT DISTINCT table1ID
FROM INSERTED
END
October 23, 2007 at 9:25 am
I have been known, when testing, to end up with the same trigger on the table twice, with different names. Are you sure the trigger is only on the table once, and no other triggers are on there?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 23, 2007 at 9:36 am
I did not think about that. I ran sp_helptrigger on my table. It returned only 2 triggers.
trg_table1_RunNightQAdbo11110
trg_#1dbo10010
the trg_table1_RunNightQA trigger is also firing twice. I looked and basically it is being handled in the nightly qa process. so whom ever developed that knew the issue but just found a work a round.
Unfortunately, I would rather do it correctly up front.
Any other ideas?
October 23, 2007 at 10:12 am
I have figured out the issue. I changed the trigger.
CREATE TRIGGER trg_#1 ON dbo.table1
FOR UPDATE
AS
IF UPDATE( dtmDateApproved)
BEGIN
INSERT INTO tbl_table2(table1ID)
SELECT DISTINCT table1ID
FROM INSERTED
END
Thanks for the help Greg. Your idea pushed me in the direction to find what I needed.
October 23, 2007 at 10:35 am
I'm glad you got it. I was just about to suggest IF UPDATE(), since I just found out about it here last week. I was also going to suggest you temporarily dump inserted and deleted into temp tables, just so could look at them to see if indeed two records were being updated, but it is a non-issue now.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 23, 2007 at 3:10 pm
tjames (10/23/2007)
I have figured out the issue. I changed the trigger.CREATE TRIGGER trg_#1 ON dbo.table1
FOR UPDATE
AS
IF UPDATE( dtmDateApproved)
BEGIN
INSERT INTO tbl_table2(table1ID)
SELECT DISTINCT table1ID
FROM INSERTED
END
Thanks for the help Greg. Your idea pushed me in the direction to find what I needed.
If UPDATE( dtmDateApproved) fixed your problem then you need to look closely on your UPDATE statement.
It's likely you update the table column by column.
I would not be surprise if somewhere soon after going to production you or you fellow DBA will post in "Administration" forum something like this: "I've got extreme growth of LOG file. Can anybody help?"
_____________
Code for TallyGenerator
October 24, 2007 at 8:05 am
Sergiy you have peeked my interest. I am sure I am walking into something here, but let me do it any way.
If I do not update a table column by column in one single update statement, that how else would you propose that I do this?
October 24, 2007 at 2:08 pm
tjames (10/24/2007)
Sergiy you have peeked my interest. I am sure I am walking into something here, but let me do it any way.If I do not update a table column by column in one single update statement, that how else would you propose that I do this?
Double call from application.
Start Profiler and do whatever you do to update the table.
See if there are 2 calls instead of 1.
_____________
Code for TallyGenerator
October 24, 2007 at 2:48 pm
Sergiy (10/24/2007)
Start Profiler and do whatever you do to update the table.See if there are 2 calls instead of 1.
Sergiy, that is freakin awesome! I had no idea what profiler was, and now I am sitting here watching transactions in real time.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 2:59 pm
So, Sergiy, I'm looking at the profiler and I see a button that opens something called 'performance monitor', that seems to be akin to the performance monitor in the task manager of any PC. I then run a procedure that I know is slow, and the lines peak at 100 for around 1 second. My question is, is it bad that a procedure causes system resources to peak at all, and if not, is one second ok to be redlined?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 3:03 pm
Also, I see a column in the profiler called 'NTUserName'. Can you tell me what table that is stored in? I have looked in what I thought would be likely system tables to no avail.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 4:16 pm
Greg Snidow (10/24/2007)
Also, I see a column in the profiler called 'NTUserName'. Can you tell me what table that is stored in? I have looked in what I thought would be likely system tables to no avail.
Did you look in master database?
It's the last table in the list.
😉
_____________
Code for TallyGenerator
October 24, 2007 at 4:22 pm
The last table I see is 'sysusers', and it is not in there. The closest I can find is sysxlogins.name, but that has the domain + '\' tacked on to the front of the NT user name, which is no big deal, I was just wondering if that is a stored value somewhere.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 4:23 pm
Greg Snidow (10/24/2007)
So, Sergiy, I'm looking at the profiler and I see a button that opens something called 'performance monitor', that seems to be akin to the performance monitor in the task manager of any PC. I then run a procedure that I know is slow, and the lines peak at 100 for around 1 second. My question is, is it bad that a procedure causes system resources to peak at all, and if not, is one second ok to be redlined?Greg
CPU is there to do come computing.
So, it's OK to give it some job to do.
Of course, in order to return result as soon as possible Server tries to use as much resources as possible.
CPU load shows that SP performs no disk operations (well, almost ;)), just heavy processing of the data in memory.
I don't know if it's OK. Probably not.
For a second modern CPU's can do a hell lot of calculations.
Typical situation causing this - nested loops in SP.
But you need to look closer and decide if it's right or wrong.
_____________
Code for TallyGenerator
October 24, 2007 at 4:24 pm
Ok, I was not looking at the master object tree when I posted. So sysxlogins is the last table, but the name has the domain tacked on to it. Does just the nt name without the domain exist any where?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply