November 12, 2008 at 11:59 am
Hello,
I'm new to SQLSERVER and I just created my first trigger. My problem is the following:
I have a table T1 with columns: C1, C2, C3. C3 is nullable. I have an after update/insert trigger that has different logic based on what is in columns C1 and C3 in the inserted table. If I do an insert into T1 with say values C1='a',C2='b',C3='c' and then next I do an update where only C1 and C2 are updated and C3 is omitted from the UPDATE statement, I get value 'c' in C3! I find it odd why it isn't NULL. I thought the Inserted table only existed during the execution of the trigger? Can I somehow truncate the inserted table?
November 12, 2008 at 12:04 pm
inserted isn't a real table. It's a pseudo table that's materialised for the duration of the trigger (only) with the values of the rows that have been inserted/updated.
Is there a default on that column?
Can you post the trigger, the table definition and the inserts that are causing this behaviour?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 12:48 pm
I thought inserted/deleted contained all the values (columns) in the table, just with the values before/after the changes.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 12, 2008 at 1:05 pm
Yeah, it will. Inserted and deleted will have the same structure as the table the trigger's definied on, excluding some of the LOB columns (I forget which ones)
If there's no default on the column and the column is not included in the insert list, the column will be null in the inserted table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 2:33 pm
I see, then I think I know what is troubling me. I assumed that columns that aren't updated in the UPDATE statement would be null in the Inserted pseudo table, but of course they contain the previous value of the row that was being updated, in other words they are unchanged... Thanks for clearing this up for me. 🙂
November 13, 2008 at 12:56 am
Indeed. With an update, the inserted table contains the rows as they are after the insert and the deleted contains the rows as they were before.
Sorry, I misread your question earlier and thought you were talking about an insert trigger, not an update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply