I am trying to set up a sql trigger. I have a customer rewards program. I need a field to update when there sales updates. Does anyone know why this trigger is kicking out errors?
CREATE TRIGGER [TR_CPNUpdate] ON [dbo].[customer] FOR UPDATE AS IF [customer.totalsales] - [customer.customnumber1] > 50 UPDATE [customer.customnumber1] SET customnumber1 = (customnumber1 + 50) GO
The errors I am getting are "Invalid column name 'customer.totalsales" & "customer.Customnumber1"
any ideas?
your brackets are in the wrong place... [tablename].[fieldname]
In most cases you don't need the brackets at all - only if the identifier is a SQL reserved work ([transaction]) of if there is an invalid character in it ([my field have includes spaces])
Glenn Adams Tiber Creek C> I am trying to set up a sql trigger. I have a customer rewards program. I
Now I am getting an error message "The multi-part identifier customer.totalsales could not be bound" I am getting the sames message in regards to customer.customnumber1
"Glenn Adams [MVP - Retail Mgmt]" wrote:
hi tommy, this trigger fire as soon as the customer any stuff, I do not know or really understand what you mean by firing 8-10 times. without any reason why should we fire this trigger. However if you have something like this then I prefer to create the store procedure to accomplish this task and make in the schedular to run every min 5,10 bla bla.
"tommyvcfs" wrote:
hi Tommy the statement is wrong first of all. why let me clear you some understanding:
your are putting the if condition IF [customer.totalsales] - [customer.customnumber1] > 50 because you never open the db so how you are running this. the correct code should be : (Rate me please)
CREATE TRIGGER TR_CPNUpdate ON dbo.customer FOR INSERT, UPDATE AS
--IF updated.totalsales - updated.customnumber1 > 50 UPDATE customer SET customer.customnumber1 = customer.customnumber1 + 50 FROM INSERTED WHERE inserted.TotalVisits - inserted.customnumber1 > 50 and inserted.ID=customer.ID GO
"tommyvcfs" wrote:
PERFECT!!! THANK YOU!!!! I have been working on this for two months. THANK YOU
Do you know how I can get thi trigger to fire multiple times? Some customers will spend up to $400. Is there a way to get this trigger to fire 8-10 times?
tommyvcfs
"Akber Alwani" wrote:
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.