@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 ENDSource - 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
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.