February 10, 2011 at 2:01 pm
Hello -
I need to write a trigger that will select the inserted table, from which I am wanting to grab values from various columns. The data from these columns will then need to get set to parameters/variables inside of my trigger, that I would like to then use to INSERT INTO another table. The problems I am having are as follows...
1. I INSERT some new rows for a customer record, and then try to simply do a SELECT * FROM inserted, but I get the following error...
Msg 208, Level 16, State 1, Line 1
Invalid object name 'inserted'.
Actually - I have tried it a couple of ways, but that is the error with a very simple SELECT.
2. There were 5 different rows INSERTED into in the same session that the attempt to pull the records from the inserted table from. I'm specifically looking for a Customer set of records, but had to make other INSERTS first, due to FKeys (which is the only reason I am describing this and not attaching code - just no pretty way to share it).
So, I am hopeful that someone can understand my issue here, and point me in a direction that will help me understand how to properly write the Trigger, and why it is not working.
Thank you all for any and all assistance!
February 10, 2011 at 2:11 pm
Keep in mind "inserted" and "deleted" objects only live _in_ the trigger.
Meaning, you cannot use that in "regular" tsql.
objects "inserted" and "deleted" have the same layout as the object you declared the trigger for.
Keep in mind to keep your triggers scope as short as possible !
books online has good examples.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2011 at 2:26 pm
I figured it out...It had to do with the way I was setting my local variable/parameter. I ended up setting them equal to the SELECT statement instead of the inserted column, and it worked.
Here's what I wound up doing to get it to work...
USE "Database Name"
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Creates new record when a Customer is created on the hardware device
CREATE TRIGGER Trg_CustomerCommand
ON Customer
AFTER INSERT
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE CloudID IS NULL)
BEGIN
DECLARE @DTCreated DATETIME
,@TreaterID INT
,@LocalID INT
SET @DTCreated = (SELECT DTCreated FROM inserted WHERE CloudID IS NULL)
SET @TreaterID = (SELECT TreaterID FROM inserted WHERE CloudID IS NULL)
SET @LocalID = (SELECT LocalID FROM inserted WHERE CloudID IS NULL)
INSERT INTO Command ([Requested],[TableID],[RecordID],[Command],[RetriesAttempted],[Cancelled],[SentDate],[ACKDate],[ResponseDate],[TimeoutDate],[SecurityUserID],[InterfaceHardwareIdentifierUsed])
VALUES (@DTCreated,'5',@TreaterID + @LocalID,'0','0','False',NULL,NULL,NULL,NULL,NULL,NULL)
END
END
GO
February 10, 2011 at 3:05 pm
You should probably try joining to the inserted table instead of just getting values from it. If there is an insert that inserts multiple records at once your trigger will only capture one record and not all of them.
_______________________________________________________________
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/
February 11, 2011 at 1:48 am
Why all that hassling with varriables ?
CREATE TRIGGER Trg_CustomerCommand
ON Customer
AFTER INSERT
AS
BEGIN
/* please add comment here */
Insert into Command ([Requested],[TableID],[RecordID],[Command],[RetriesAttempted],[Cancelled],[SentDate],[ACKDate],[ResponseDate],[TimeoutDate],[SecurityUserID],[InterfaceHardwareIdentifierUsed])
Select DTCreated,'5',TreaterID + LocalID,'0','0','False',NULL,NULL,NULL,NULL,NULL,NULL
from inserted
WHERE CloudID IS NULL
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2011 at 9:42 am
Sean, it's not a situation that will ever see more than 1 record being updated at a time (this is on a smaller sized version of our PROD db. It's driven by a touchscreen device, and will never batch.
The reason I bothered with all the variables is because I couldn't get my data via the INNER JOIN was I was attempting to do. I didn't post it up here because I got it figured out with setting the parameters the way I did, instead (don't know that I even have a copy of how I was working with with the INNER JOIN.
Sorry about that, but thanks to you both!
🙂
February 11, 2011 at 9:48 am
Rich Yarger (2/11/2011)
Sean, it's not a situation that will ever see more than 1 record being updated at a time (this is on a smaller sized version of our PROD db. It's driven by a touchscreen device, and will never batch.🙂
I have bitten by this more than once. I create a simple little app that will do nothing more than use a simple interface to enter one record at a time. Works great until that one day when there is a bug and the system goes down. Then I get a spreadsheet with 100 entries and wanting to minimize the work i do an insert table using a select for all 100 records. oops my trigger missed 99 of them.
Looks to me like ALZDBA's approach is simple, clean and will handle those accidental one time batch inserts that are totally unplanned but happen anyway.
_______________________________________________________________
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/
February 11, 2011 at 2:21 pm
Like Sean stated, many of us have been bitten by the "single row situation"-design of many triggers.
You should ( and I don't like to use "always" nor "never") in all cases develop your triggers to cope with more than a single row !
Maybe, just for now, the situation looks like it will always be a single row usage, but the future is still to come and figuring out a bogus trigger is not that simple.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2011 at 12:33 pm
Sean and ALZDBA - you guys are right. I think this is what I am going to do.
Also - what do you think about a select case statement, instead of me making a different trigger (with this same logic) for a different table, each time it is needed? There should be any other occasions for which that would be the case, but I did get a list from one of my developers just recently, which had 25 different tables as TableIDs (which is what drives this process).
To date - I only have 3 of them done, as needed.
SELECT CASE I think is a better way for me to go, but I'd like to hear both of your thoughts on that one.
Thanks again!
February 12, 2011 at 11:18 pm
a trigger is declared on an object and objects don't share triggers (afaik).
So you'll end up creating the trigger ( same or different content ) as n triggers, at least 1 per table.
You could say: "ok I'll create a sproc and call that one from within the trigger"
It's a choice.
The only thing I can say is "Keep your triggers scope as small as possible !"
If a trigger fails, your whole transaction fails and will be rolled back !!
I try to keep a triggers code as small as possible and as straight forward as I can.
The choice to be made is " what _must_ be done in the trigger transaction scope " and what can be performed afterward. (scheduled background process, or SSB application, alert response ...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 14, 2011 at 9:26 am
You know what? I just realized why I was going with variables/parameters. I need to combine 2 columns, so that the values are separated by a dash/hyphen... '-'.
I have been trying to get it to work with your example ALZDBA, but am not having a lot of success.
Here is what I started with. . .
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Trg_BatchCommand]'))
DROP TRIGGER [dbo].[Trg_BatchCommand]
GO
--Creates new Command table record when a Batch record is updated on the Treater. ~RWY 2/11/2011
CREATE TRIGGER Trg_BatchCommand
ON Batch
AFTER UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE DTEnd IS NOT NULL)
BEGIN
DECLARE @DTStart DATETIME
,@TreaterID INT
,@BatchID INT
,@RecordID VARCHAR(255)
SET @DTStart = (SELECT DTStart FROM inserted WHERE DTEnd IS NOT NULL)
SET @TreaterID = (SELECT TreaterID FROM inserted WHERE DTEnd IS NOT NULL)
SET @BatchID = (SELECT BatchID FROM inserted WHERE DTEnd IS NOT NULL)
SET @RecordID = CAST(@TreaterID AS varchar(5)) + '-' + CAST(@BatchID AS varchar(5))
INSERT INTO Command ([Requested],[TableID],[RecordID],[Command],[RetriesAttempted],[Cancelled],[SentDate],[ACKDate],[ResponseDate],[TimeoutDate],[SecurityUserID],[InterfaceHardwareIdentifierUsed])
VALUES (@DTStart,'3',@RecordID,'0','0','False',NULL,NULL,NULL,NULL,NULL,NULL)
END
END
GO
Your example is a lot better, and especially since it will take multiple records into consideration, but I am not having any luck, getting away from having to still define some sort of variable/parameter, to get the columns to combine.
Any other thoughts?
February 14, 2011 at 9:35 am
Something like this
Insert into Command ([Requested],[TableID],[RecordID],[Command],[RetriesAttempted],[Cancelled])
Select DTCreated, '3', cast(TreaterID as varchar(5)) + '-' + cast(BatchID as varchar(5)), '0', '0', 'False'
from inserted
WHERE CloudID IS NULL
Notice I also removed the 6 columns at the end where you were explicitly inserting NULL. It doesn't hurt anything but just adds a lot of code.
_______________________________________________________________
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/
February 14, 2011 at 9:43 am
Sean, thank you! That's got it! So I will now have all the safeguards or being able to do multiple INSTERTS or UPDATES (as I modifiy this for other triggers), and a lot less code. Many thanks for that!
The only other thing I was wondering about is if I shouldn't have a ROLLBACK at the very end?
Regardless - thank you very much (both you and ALZDBA).
February 14, 2011 at 9:47 am
I would think you don't want a rollback unless there is something else I am missing. A rollback would rollback all the stuff you just did.
_______________________________________________________________
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/
February 14, 2011 at 10:18 am
OK - just wondering (as all of the triggers I've written to date have not been on isoldate installs as this one will be). The intention of the trigger is infact to fire - not be rolled back, so good deal.
Sean, thank you again.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply