March 3, 2012 at 8:40 am
Sql novice here, need help with an insert trigger please.
I have a 'Customer' database with customerid, name, email...
Attempting to create an insert trigger to create a CustomerLog table. When a customer places an order, i'll pass in the customer id, which should do the following:
- CustomerLogID will autocount
- CustomerID (pass in)
- email, should pull from 'Customer' database that corresponds to customerid
- get date.
What values do I enter in the "Insert Into" line of the create trigger query?
-- INSERT TRIGGER
Create Table CustomerLog (CustomerLogID int Identity, CustomerID int, Email varchar(100), LogDate varchar(20))
CREATE TRIGGER tgrCustomerInsert ON Customer FOR INSERT
AS
DECLARE @CustomerID int
Select @CustomerID = CustomerID From inserted
INSERT INTO CustomerLog (CustomerLogId, CustomerID, Email, LogDate)
Values(CustomerLogId, @CustomerID, Email, GetDate())
GO
Insert Into CustomerLog (Id) Values(?)
Select * From CustomerLog
March 3, 2012 at 8:46 am
lfc8ynwa (3/3/2012)
DECLARE @CustomerID intSelect @CustomerID = CustomerID From inserted
The first problem is that you are assuming there will only ever be one row inserted at a time. What's going to happen if inserted has 2 rows?
Typically, if you want to create a logging trigger, it will look something like the following.
CREATE TRIGGER....
AS
INSERT INTO <logging table name> (<columns to insert>)
SELECT <columns> from inserted
Try using that kind of pattern. You shouldn't be assigning variables from inserted because the trigger will 'break' if there are multiple rows 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
March 3, 2012 at 8:52 am
Ok. thanks.
Any help on the values to enter in the Insert Into line?
March 3, 2012 at 9:57 am
See my previous post, specifically
Typically, if you want to create a logging trigger, it will look something like the following.
CREATE TRIGGER....AS
INSERT INTO <logging table name> (<columns to insert>)
SELECT <columns> from inserted[/code]
Try using that kind of pattern. You shouldn't be assigning variables from inserted because the trigger will 'break' if there are multiple rows in the inserted table.
The columns you want to use in the select portion of the insert statement are the columns you want to log from the table being inserted into (you said customerID and Email)
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
March 3, 2012 at 10:06 am
My problem is I don't know what physical code to type in on the values line.
INSERT INTO CustomerLog (CustomerLogId, CustomerID, Email, LogDate)
Values(CustomerLogId, @CustomerID, Email, GetDate())
When I enter the preceding code in, and attempt to execute it, I get the following error:
Msg 128, Level 15, State 1, Procedure tgrCustomerInsert, Line 8
The name "CustomerLogId" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I don't know what to put instead of CustomerLogId or Email in the values line.
March 3, 2012 at 10:10 am
Please look at the rough code I gave you. I didn't use Insert .. values anywhere.
If you are writing a trigger to log inserted rows into another table you cannot use Insert ... values as it will give incorrect results when there are multiple rows. That's why I wrote you some pseudocode to use.
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
March 3, 2012 at 10:18 am
Thanks but unfortunately I'm either not smart enough to figure that out or it's just not creating what I need it to. My assignment was to only passthrough the CustomerID from the Customer database and all the remaining information would come in.
I guess thats why i'm just a beginner.
March 3, 2012 at 10:26 am
Which part of the rough sample code that I posted don't you understand?
Forget about passing customerIDs around, that's not how triggers work. The rough code I posted would be the entirety of the trigger, no other selects, no inserts, no variables. Just a single insert statement
Edit: And maybe have a quick read up in books Online (the SQL help file) about triggers.
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
March 3, 2012 at 10:30 am
I guess the rough part now is passing the ID in is how I was instructed specifically to create the trigger.
I'll take a look at the books.
Thanks.
March 3, 2012 at 10:48 am
Well you can't pass an ID to a trigger, and pulling an ID out of the inserted table into a variable is a common error when writing triggers as if results in incorrect behaviour when multiple rows are in the inserted table (as in a multi-row insert). That's why the rough code I gave does not do that.
So, to repeat, a trigger that fires after insert to a specific table and inserts some of the just-inserted values into another table generally looks like this:
CREATE TRIGGER <trigger name here>
ON <table that is being inserted into>
AFTER INSERT
AS
Insert Into <Logging table name> (<columns in the logging table>)
SELECT <matching columns from the table being inserted into>
FROM inserted
-- end of trigger
GO
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
March 4, 2012 at 9:48 am
Maybe it shouldn't be an insert then, an update trigger instead. Attached is my specifics.
March 4, 2012 at 10:18 am
If the trigger needs to fire after a row is added to customer, it's an insert trigger, if fires after an insert operation. An update trigger fires after a row is modified by an UPDATE statement.
Besides, everything I've said is relevant to update triggers as well.
p.s. That exercise looks like homework, which means I am not going to do it for you. I'll advise, but that's all.
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
March 4, 2012 at 5:45 pm
Yes, this is homework. And unfortunately, it's easy to see that I didn't understand my one example given.
Not looking for the answers, I want to figure this out myself, as i'm sure it'll be prevalent in future assignments.
I'm just trying to decipher between the example I was given (following) and your examples.
-- INSERT TRIGGER
Create Table AddLog (TableName varchar(30), AddDate datetime,
Name varchar(50))
CREATE TRIGGER tgrTableInsert ON TableA FOR INSERT
AS
DECLARE @TheName varchar(50)
Select @TheName = Name From inserted
INSERT INTO AddLog (TableName, AddDate, Name)
Values('TableA', GetDate(), @TheName )
GO
Insert Into TableA (Id,Name) Values(11,'Grape')
Select * From AddLog
March 5, 2012 at 1:31 am
User ZIYkkE22ol has been reported.
March 5, 2012 at 1:52 am
Now that you've explained that this is for study purposes, we'll be able to give you the information on a level that you will be able to understand.
You'll have to look up the information on the T-SQL "insert" statement to see Gail's point. Your best reference for Microsoft SQL server is Microsoft's web site called Books Online (often refered to as BOL). The information for the insert statement can be found here: http://msdn.microsoft.com/en-US/library/ms174335(v=sql.105).aspx.
You are likely only used to see:
insert (col1, col2)
values ( 'val1', 'val2') .
The far more often used syntax is however:
insert (col1, col2)
select 'val1', 'val2'
The difference being as Gail was trying to explain, that the values()-variant only works for a single row of data, whereas the select-variant may insert virtually any number of rows in a single statement.
edit: While re-reading my examples I saw that I made some serious errors in them, that will not help you in getting the idea. So I have replaced them with some better ones:
insert using values() as in your example:
DECLARE @TheName varchar(50)
Select @TheName = Name From inserted
INSERT INTO AddLog (TableName, AddDate, Name)
Values('TableA', GetDate(), @TheName )
And the way it should have been presented, using the insert-select syntax:
INSERT INTO AddLog (TableName, AddDate, Name)
select 'TableA', GetDate(), i.Name
from inserted i
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply