|
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
|
