|
Hi,
How can I bypass an SQL Server 2005 TRIGGER with no potential concurrency issues? The trigger is executed ON UPDATE, where the table is updated by an ASP.Net web application... so there will be multiple users concurrently using this specific function.
So far I have found 3 possible solutions, but I don't know if there will be concurrency problems with these solutions.
1. Disable the trigger at the beginning of the update procedure, and enable it at the end <-- definitely has concurrency issues
2. Using CONTEXT_INFO() http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AuditOrBypassTriggerExecution[^] <-- not sure about concurrency problems with this solution
3. Using a #tempTable http://www.mydatabasesupport.com/forums/sqlserver-programming/201598-ignoring-trigger.html[^] <-- see last post by Erland Sommarskog, not sure about concurrency problems with this solution
What do you think? Or are there other better solutions?
Thank you in advance.
Rafferty
|
|
|
|
|
I have to check on a couple of things and was thinking of creating about 200million dummy records. I had a for loop and an insert statement in mind. Is there a faster way of creating and inserting such a mass volume of data into a database?
|
|
|
|
|
What database system? Does it have a bulk insert option?
I often use a for or while loop and a parameterized insert statement.
|
|
|
|
|
SQL Server.... Forgot about the bulk insert...I could use that with the for loop.
|
|
|
|
|
|
You cold use something like:
insert into table xxx
select '1', 3 , 'testdata'
from northwind..customers a, northwind..customers b, northwind..customers c
add more northwind x's to get more records.
If you don't have the northwind database, any other (big) table on the sqlserver can be used.
Wout Louwers
|
|
|
|
|
hi all,
i m trying to make a trigger for the database and i dont know how first where to write it?
and for example i make a master table and 3 other tables
Master_Table : will be in it all the actions
Table_1 : when insert a new record the max(ID) and the table name will be wriiten in the Master_Table
Table_2 : when update a record the (ID)of the updated record and the table name will be wriiten in the Master_Table
Table_3 : when delete a record the (ID)of the deleteded record and the table name will be wriiten in the Master_Table
i make this but it still gives me an error when execute but when i comment all of it except TriggerExInsert it works
CREATE TRIGGER TriggerExDelete<br />
ON Table_3 <br />
AFTER DELETE<br />
AS <br />
BEGIN<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
INSERT INTO MasterTable(ID,ActionA)<br />
SELECT ID, 'DELETE'<br />
FROM deleted<br />
<br />
END<br />
GO<br />
<br />
CREATE TRIGGER TriggerExUpdate<br />
ON Table_2 <br />
AFTER UPDATE<br />
AS <br />
BEGIN<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
INSERT INTO MasterTable(ID,ActionA)<br />
SELECT ID, 'UPDATE'<br />
FROM inserted<br />
<br />
END<br />
GO<br />
<br />
CREATE TRIGGER TriggerExInsert<br />
ON Table_1 <br />
AFTER INSERT<br />
AS <br />
BEGIN<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
INSERT INTO MasterTable(ID,ActionA)<br />
SELECT ID, 'INSERT'<br />
FROM inserted<br />
<br />
END<br />
GO<br />
<br />
Thanx a lot for the help! 
modified on Wednesday, May 20, 2009 10:10 AM
|
|
|
|
|
Beesan, I'm not going to help you with the triggers - I don't hate you that much.
Triggers are EVIL - they are difficult to very support, imagine 2 years from now when you look at the master table and wonder where all the data comes from. When they go wrong it can have dramatic, negative affects on your system.
I believe you have a design problem. Master seem to be acting as a log type table, I think a better solution is to have the logging data with the record IE add the created/modified dates to each table and use a view or query to get the information from the individual tables.
There are a number of logging solutions around, triggers are one of the nastier methods of achieving this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
Please help me with a querry
I have 2 table (A and B) which are related with id (foreign keys.)
Now I want to view all the ids of table A which are not there in table B.
thanks in advance
-----------------------------
I am a beginner
|
|
|
|
|
try this:
SELECT A.PrimaryKey
FROM A
WHERE A.PrimaryKey NOT IN
(
SELECT B.ForeignKey
FROM B
)
Phil
I won’t not use no double negatives.
|
|
|
|
|
SELECT T1.ID
FROM T1
LEFT OUTER JOIN T2
ON T1.ID=T2.ID
WHERE T2.ID IS NULL
|
|
|
|
|
We offer you the best traffic campaign to instantly increase your web traffic & sales. We use breakthrough technology to submit your ads and instantly put your product in front of millions of potential customers. Visit us at: http://www.clicknearn.net/2385-57.html
|
|
|
|
|
If you want to advertise, contact the site administrators and pay for it, like normal intelligent people do.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
i am developing a windows application with VS 2005
a i need to populate a combo box with the
column value from my table in the database
i'm using a stored procedure and anytime
i try de code in visual studio i do have
a violation of primary key constraints error
but when i execute the stored procedure in
my query analyzer, it returns the real values
please can someone tell why this is happening??
Jondo
|
|
|
|
|
Jondo24shoots wrote: please can someone tell why this is happening??
Very unlikely without seeing at least part of the code that populates the combo box.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi,
Are you using any insert, update and delete statement? Otherwise, it should not show primary key violation error for a select statement. Please post the code as Henry rightly said.
Regards
Saanj
Either you love IT or leave IT...
|
|
|
|
|
Hi,
I am new to sqlserver2000 , so experts please excuse, I am very much stuck in deploying the database created locally to a remote server. Please help me out ,by providing me the basic detail regarding the process of making the local db into a remote db, i've the ip-addr,usrname,psw of the web service provider.
Plz help...
|
|
|
|
|
Does your web service providor support SQL 2000.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am working on a customized user function in MS Excel that breaks up the calendar into thirteen 28-day periods. The objective is to have as many 28 day periods in a row as possible. The standard monthly calendar consists of months in which some months have 30 days, while other months have 30 days, and occasionally there is a leap year with 28 days in February. However, for the month of December in the years 2009 and 2015, there are 35 days instead of 28 days.
In a spreadsheet, place the date 12/30/2007 and then pull the drag handle down so that the last cell is 12/31/2016. To the right of each of these cells is the place where the function cell result should be placed.
Now we need to create the place to add the code for a customized user function. This can be accomplished by pressing the Alt-F11 at the same time. A window opens in which code can be written for the function. Select "Insert" > "Module". Now there is a place to put the code.
Here is the code for the custom user function:
Option Explicit
Public Function ZodiacPeriod(dteInputDate)
Dim dteStart As Date
Dim dteEnd As Date
Dim intDateCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim intX As Integer
Dim intCounter As Integer
Dim intYear As Integer
Dim arrArray(3290, 1)
dteStart = #12/30/2007#
dteEnd = #12/31/2016#
intDateCount = DateDiff("d", dteStart, dteEnd)
For intRow = 0 To intDateCount
arrArray(intRow, intColumn) = dteStart
dteStart = dteStart + 1
Next
intX = 1
intCounter = 1
For intRow = 0 To 3290
arrArray(intRow, 1) = intX
If (Year(arrArray(intRow, 0)) = 2009) Or _
(Year(arrArray(intRow, 0)) = 2015) Then
If (Month(arrArray(intRow, 0)) = 12) Then
If intCounter <= 35 Then
If intX >= 14 Then
intX = 1
End If
intCounter = intCounter + 1
Else
intX = intX + 1
intCounter = 1
End If
Else
If intCounter <= 27 Then
If intX >= 14 Then
intX = 1
End If
intCounter = intCounter + 1
Else
intX = intX + 1
intCounter = 1
End If
End If
Else
' Normal processing
If intCounter <= 27 Then
If intX >= 14 Then
'What follows after 28th value of 13.
intX = 1
End If
'What follow the first cell formula is applied to
intCounter = intCounter + 1
Else
intX = intX + 1
intCounter = 1
End If
End If
Next intRow
'Now to go through the array and return the value
For intRow = 0 To 3290
If arrArray(intRow, 0) = dteInputDate Then
ZodiacPeriod = arrArray(intRow, 1)
Exit For
End If
Next intRow
End Function
Let us place the cursor into the cell into which the formula should be placed. Select "Insert" > "Functions" from the main menu and submenu.
Select the function name "ZodiacPeriod", and in the input box that pops up provide the left-most column in the spreadsheet that includes the date. Drag the drag handle on the bottom right corner of the cell to the bottom of the data thereby applying the formula to the other cells in the table. A related article can is as follows: http://office.microsoft.com/en-us/excel/HA011117011033.aspx?pid=CL100570551033[^]
Here is the question: Why is there a period 14 following the last 13th period?
modified on Tuesday, May 19, 2009 9:27 PM
|
|
|
|
|
|
Sod off and pay for advertising, don't spam forums.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I'm having a slight brain fart today, but maybe somebody can help me. I need to get a tariff, given an effective date in a tariff table, as follows:
create table tariff
(
tariffid int identity,
tariffdate smalldatetime,
tariff int
)
The best I can come up with is
select max(tariffid) from tariff where '20090501' > tariffdate
where '20090501' is the date I want to find the tariff for, but my query doesn't work in this case because '20090501' is less than my first effective date.
You really gotta try harder to keep up with everyone that's not on the short bus with you.
- John Simmons / outlaw programmer.
|
|
|
|
|
So if '20090501' is less than the first effective tarrif date, then there is no tarrif to be charged. Right? For example,Does this mean that on January 1, 2010 there will be a tarrif ?
I'm not sure what the problem is.  Maybe you could list some sample data.
BTW: I've used effective dating logic for payroll where we had to determine what the pay rate was for an employee and we found that to make the algorithm simpler we always had a very large date as the final entry so that our query would always work.
For example, a new employee is hired on Jan 1, 2009 with a payrate of $10/hr
The effective payrate records looked something like this:
EffectiveFromDate 20090101
EffectiveToDate 29991231
Rate 10.00
Maybe that will help you with your algorithm. 
|
|
|
|
|
David Mujica wrote:
So if '20090501' is less than the first effective tarrif date, then there is no tarrif to be charged. Right?
That is actually right. Like I said, I was having a dull moment, where I was thinking records earlier than the 1st tariff should default to the 1st tariff. I'll raise an error if the user tries to insert without a tariff.
On Jan 1 2010, the effective tariff will be the last tariff in the table. I only have one date to work with, e.g.
tariffid tariffdate tariff
1 2009-05-02 00:00:00 100
2 2009-05-04 00:00:00 150
3 2009-05-05 00:00:00 70
4 2009-05-10 00:00:00 90
You really gotta try harder to keep up with everyone that's not on the short bus with you.
- John Simmons / outlaw programmer.
|
|
|
|
|
you are disrupting the markets. We need more stability, not less. 
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
