January 17, 2012 at 1:56 pm
I am creating a trigger that is based on a column
if update(column).....
The question is can I continue the conditions like such?
If update(columnA) AND exists (select 1 from inserted i join deleted d on d.indexA=i.indexA and d.columnA = x and i.columnA = y)
Basically I only want the trigger to fire if columnA changes from X to Y.
January 17, 2012 at 2:21 pm
yes...
...of course the trigger will always fire, your condition is just not going to execute the code inside your if block. Splitting hairs perhaps but very important to understand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2012 at 3:37 pm
That is what I thought. I understand that the trigger would fire on any update to the specified table, but I just wasn't sure if you could extend the if statement, no talk about it in BOL or ONLINE. So my above statement would work just fine, and only complete if the column was changed from X to Y? If not from X to Y then nothing would execute and the trigger would close.
January 18, 2012 at 7:14 am
Yeah that should work just fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2012 at 8:58 am
Thank you for the verification. No matter how many years you do this you always come across something new.
😎
January 20, 2012 at 12:52 pm
Actually the test IF UPDATE(ColA) will always test true for an INSERT (if the trigger happens to be for both UPDATE and INSERT) and will always test true if ColA is in the update statement itself. The column could be the same value as it was before the update - UPDATE Table SET ColA = ColA will always test true.
Also your code example looks like it assumes a single row update - something I commonly see in triggers.
Here's a proof of concept using your simple example:
CREATE TABLE Test
( TestID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, ColA CHAR(1)
, ColB VARCHAR(50)
)
GO
CREATE TABLE TestLog
( TestLogID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, TestID INT
, TestResults VARCHAR(50))
GO
-- This shows that IF UPDATE(ColA) always tests true for an INSERT.
CREATE TRIGGER Test_IU ON Test AFTER INSERT, UPDATE
AS
IF UPDATE(ColA)
BEGIN
INSERT INTO TestLog (TestID, TestResults)
SELECT I.TestID, 'Update Tested True'
FROM inserted I
END
GO
INSERT INTO Test
( ColA, ColB )
VALUES ('X', 'First Insert')
GO
-- UPDATE tested true
SELECT * FROM TestLog
-- Set ColA to itself
UPDATE Test
SET ColA = ColA
-- Also shows true for IF UPDATE
SELECT * FROM TestLog
For this reason I don't actually use IF UPDATE all that often. I prefer to just compare the inserted and deleted record sets to get what I want. Something like:
-- Don' do the trigger after INSERT. That was only an example
ALTER TRIGGER Test_IU ON Test AFTER UPDATE
AS
-- We only want rows where ColA changed from 'Y' to 'X'
SELECT I.TestID, I.ColA, I.ColB
INTO #TestTemp
FROM inserted I
INNER JOIN deleted D ON
I.TestID = D.TestID
WHERE
I.ColA = 'X'
AND D.ColA = 'Y'
IF @@ROWCOUNT = 0
RETURN
INSERT INTO TestLog
( TestID, TestResults )
SELECT
I.TestID, 'Changed from Y to X'
FROM inserted I
-- Other processing here on #TestTemp
GO
TRUNCATE TABLE TestLog
-- Set ColA to itself
UPDATE Test
SET ColA = ColA
-- Nothing done in trigger
SELECT * FROM TestLog
-- Change from 'X' to 'Y'
UPDATE Test
SET ColA = 'Y'
-- Still no action done.
SELECT * FROM TestLog
-- Change from 'Y' TO 'X'
UPDATE Test
SET ColA = 'X'
-- Now we get something to do.
SELECT * FROM TestLog
Todd Fifield
January 20, 2012 at 2:59 pm
It's a simple enough trigger. I am not changing any data, rather just moving data. In essence this is what I am doing.
Similair to a history table I am copying information from one table to staging table if a clients status changes from a certain type to another, but only on a specific change. I have tested the trigger quite extensively and it appears to be working great. Regardless of whether it is an insert/update the clauses are not executing unless the status change meets the requirement. Looking at the deleted table to see the previous status and inserted to see the change.
As mentioned earlier by others, I too try to avoid the use of cursors and triggers, but there are instances where you have no choice and often perform better than alternatives(#tables, @Table, etc).
I also agree to needing accountants/lawyer if I was using an INSTEAD OF trigger, or changing data. No Enron'ing here :-). Thanks for the concern though.
Creating a #table to look through the inserted and deleted table when dealing with hundreds of thousands of entries an hour would be a beast on resources and not very effecient though it would work but run more times than need that is why the Update(), insert(),... are there.
Again thanks all for your comments. I hope this helps someone down the line.
January 21, 2012 at 12:41 pm
mgchris (1/20/2012)
Creating a #table to look through the inserted and deleted table when dealing with hundreds of thousands of entries an hour would be a beast on resources and not very effecient though it would work but run more times than need that is why the Update(), insert(),... are there.Again thanks all for your comments. I hope this helps someone down the line.
Actually, I've found through testing that the simple SELECT INTO #temp, limited to only what I want in a trigger runs faster than the IF UPDATE(ColA) followed by AND EXISTS(...) type construct. You will notice from my example only the rows where ColA changed from 'Y' to 'X' are dumped into the temp table. That's what you want to process or dump into a log table or whatever.
And take anything from Joe with a grain of salt. I don't think he works in the real world for real clients like the rest of us. He thinks that rows aren't really records a database. The people who actually sign our checks think of them as records rather than rows. I'll take the signed check any time.
Todd Fifield
January 22, 2012 at 1:06 am
CELKO (1/21/2012)
That is another thing we do not like in RDBMS. Redundancy and physical moving of data like it was still on punch cards. WE use VIEWs, COMPUTED and REFERENCES instead. The mantra is “One fact, one way, one time, one place”
Who's this "we" you're speaking of? The Ivory Tower board of computing? Leave "us" out of your "we"'s until you've spoken with "us".
Also, One fact, one way,... yeah, one place as source of record, everything else as copies for faster reporting. Warehouses break this rule all day long.
To track the history of, say, Foobars we need to sde time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton.
An excellent technique for a historical logging table. Horrible if you have a lot of changes and need to go seeking through it for "current" entries. In most cases the pain of a little extra work during a data change is a lot less painful than taking extra pain during the 100s of reads that datapoint will receive.
No, it does not work great. How much information does the optimizer get from it? None. Can it port? Nope.
While I won't argue the optimizer doesn't get information from it, you have combined two opposite concepts here. A) An optimizer is product specific. B) Portable code isn't. Will you PICK one to care about please, Mr. ISO? That, and who the heck really cares if it ports? I don't commonly take my SQL databases over to MySQL for giggles every now and then.
I have written five triggers in my career. I had CASE and DRI actions, I know I could have avoided three of them. So based on personal experience over three decades of SQL programming, I doubt it today. Can you show me an example? Fixing bad DDL is not a GOOD REASON 🙂
Audit tables are though.
LOL! We put INSTEAD OF into ANSI SQL to handle the NP-complete nature of VIEW updating. That way a VIEW would behave like abase table.
Again with the "we". Now, let me understand this, you (meaning the ISO/ANSI standards board) put in a mechanism to allow for the treatment of views, which are at heart a differently organized display of base data, to act like storage mechanism. And then, while professing to prefer views, you have only written five of these triggers, 3 of which were unnecessary?
*blink* Ooooooooooookaaaaaaayyyyy......
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 22, 2012 at 11:42 pm
While Celko may be rude and he comes across as though he's never left the ivory tower to do real work, there is a small point in what he says. Make sure you are not using triggers as a replacement for good design. In certain cases (such as performance considerations) when you must implement less than theoretically perfect architecture, they can be handy but don't fall into the trap of letting them be a crutch.
January 23, 2012 at 9:27 am
I agree that there are few times when a trigger is better than a view or more pragmatic programming. However, in this case it is neccessary. I am doing a bit of customization of a program that does not have the data items that I need to fill a requirement from a client. The program allows for custom table creation with front end programming automated. This allows me to create a table that holds all the data that I need with the ability to add data entry items. With that in mind a view is not an option where data entry is required. Stored procedures are also not an option. The only options where redundancy in both data and data entry or a trigger that will copy data from the primary table to the new table that will allow more information.
Yes could I have just updated the primary table to hold the columns of interest? Of course but I would lack to ability to include data entry for the front end user as the primary table was preprogrammed to only hold the current schema. The support I spoke of is for new tables only that are created in the front end CRM. With that I didn't want double redundancy in both data and data entry. Yes is my method dirt and not always optimal? Of course. Sometimes you do what you have to do to get the job done.
When in conversation with the software vendor their solution would have been the same thing only exponentially expensive.
Thankfully I am not worried about portabilty. I will never have to move to another platform, it is a SQL SERVER non express version required program. I could take it to Oracle but the company does not desire to assume the costs of moving over to Oracle when the benefits would be minute if any. (Not saying Oracle is not good just not a benefit in THIS case).
Not too worried about people being "rude" or 'ivory towered". Celko does have practical information and he does have a point and in an ideal situation it is very sound advice. In my 6 years as a DBA this is my first trigger I felt I had to write (outside of formal schooling). I have wanted to write others but found the above information to be the best as well.
Anyway lets not bicker and argue over who T-SQL'd whom. This is a happy occasion. I got some advice and have a trigger that is working as was intended. Though not ideal this trigger founds its place. Even triggers need to feel needed ;-).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply