I am trying to write a trigger that will only fire when certain fields are updated. I can't seem to find any threads that relate to this issue. Below is the trigger syntax. Any info would be great. Thx
CREATE TRIGGER tr_feedback_date ON Calls
FOR UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if update (feedback) or update (analyst) or update (coordinator) or update (technical) or update (fixtime)
or update (phonewait) or update (resp)
UPDATE hold_complete
set hold_complete.feedbackdt = getdate()
from inserted with (nolock)
where hold_complete.fkey = inserted.callidThe create trigger statement can take an IF UPDATE ( column ) clause. You can read about it in Books Online.
Another option it to compare the values in the INSERTED table to the values in the DELETED table. Since updates involve both insertions and deletions, your affected record will exist in both and you can compare the differences based on the primary key.
blindman|||Also, there is very useful function COLUMNS_UPDATED() which you can use in trigger for checking what fields were updated.
Showing posts with label threads. Show all posts
Showing posts with label threads. Show all posts
Monday, March 19, 2012
Tuesday, February 14, 2012
Concurrency Issues
Is there any way to get the sample below working so that both "threads" are guaranteed to get unique and incrementing values?
I'm suspecting the answer is no. You can use transactions on completely database oriented operations that read/write to a database and complete. But there aren't complete synchronization controls for operations like below that try to return a value to an outside process.
IF OBJECT_ID('SimpleTable') IS NOT NULL
DROP TABLE SimpleTable
CREATE TABLE SimpleTable (
A INTEGER
)
INSERT INTO SimpleTable (A) VALUES (1)
-- Run in one window
DECLARE @.value INTEGER
BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
WAITFOR DELAY '00:00:05'
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION
SELECT @.value
SELECT A FROM SimpleTable
-- Run in a second window
DECLARE @.value INTEGER
BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION
SELECT @.value
SELECT A FROM SimpleTableUse an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.
-PatP|||Use an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.
-PatP
Actually, I did just that. However, I was kind of curious if it was possible to acheive with transactions or with some explicit locking calls.|||There definitely is a way to do it using just SQL statements with Transact-SQL's locking model. The identity process is simpler and supported though.
-PatP|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.
Thank you. That will work. Except BOL calls those locking "hints" which means the solution isn't guaranteed to work even though it probably will on today's implementations.|||I don't see how it will not work, but you're free to refute it.|||It won't work if a future implementation (either a future version or service pack of SQL Server) decides to ignore the locking "hint". The database engine is completely free to obey or ignore "hints" at will so you shouldn't base an algorithm on that.
I don't see how it will not work, but you're free to refute it.
I'm suspecting the answer is no. You can use transactions on completely database oriented operations that read/write to a database and complete. But there aren't complete synchronization controls for operations like below that try to return a value to an outside process.
IF OBJECT_ID('SimpleTable') IS NOT NULL
DROP TABLE SimpleTable
CREATE TABLE SimpleTable (
A INTEGER
)
INSERT INTO SimpleTable (A) VALUES (1)
-- Run in one window
DECLARE @.value INTEGER
BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
WAITFOR DELAY '00:00:05'
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION
SELECT @.value
SELECT A FROM SimpleTable
-- Run in a second window
DECLARE @.value INTEGER
BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION
SELECT @.value
SELECT A FROM SimpleTableUse an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.
-PatP|||Use an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.
-PatP
Actually, I did just that. However, I was kind of curious if it was possible to acheive with transactions or with some explicit locking calls.|||There definitely is a way to do it using just SQL statements with Transact-SQL's locking model. The identity process is simpler and supported though.
-PatP|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.
Thank you. That will work. Except BOL calls those locking "hints" which means the solution isn't guaranteed to work even though it probably will on today's implementations.|||I don't see how it will not work, but you're free to refute it.|||It won't work if a future implementation (either a future version or service pack of SQL Server) decides to ignore the locking "hint". The database engine is completely free to obey or ignore "hints" at will so you shouldn't base an algorithm on that.
I don't see how it will not work, but you're free to refute it.
Labels:
below,
concurrency,
database,
guaranteed,
incrementing,
microsoft,
mysql,
oracle,
sample,
server,
sql,
suspecting,
threads,
unique,
valuesi,
working
Subscribe to:
Posts (Atom)