Q
Problem solve Get help with specific problems with your technologies, process and projects.

Problem creating trigger to update reservations table

I am having a problem with a trigger to automatically update a table. I have a table, reservations: customerid, checkindate, checkoutdate, servicecharge, totalcharge. And I have a table called transactions: transactionid, customerid, mservicecharge, and servicedescription. Now I want a trigger like:

@service = inserted.mservicecharge from inserted (which is Transactions table)
so if @servicecharge > 0
update reservation set servicecharge = mservicecharge + @insert
then the join statement.

But I keep getting errors like: ambiguous column name 'servicecharge'. I am using SQL Server 7.0.

Based on the pseudo code, I believe the trigger problem is related to table aliasing. SQL Server is not sure which table the servicecharge column relates to and generates the error you are receiving. By aliasing the table, it is possible to identify the column via a single letter or you can type the complete table name. Here is an example directly from SQL Server 2000 Books Online that may be helpful between the employee and jobs table in the Pubs database:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'employee_insupd' AND type = 'TR')
   DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
SELECT @min_lvl = min_lvl, 
   @max_lvl = max_lvl, 
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 
   JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
   RAISERROR ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
END
Source - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

After reviewing the code you can see that the employee table with all of the related columns are aliased with an 'e'. The jobs table with all of the related columns are aliased with a 'j'. This way SQL Server knows which tables to reference in the code i.e. inserted, deleted or user defined tables.

For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close