February 5, 2011 at 8:02 am
Good morning all,
I am trying to crete a trigger that will give an error after insert then rollback the transaction. Just as an aside...I've never created a trigger but I am determined to figure this out! When I try to execute the creating of the trigger I get the following errors:
Msg 4104, Level 16, State 1, Procedure pt_check_department, Line 18
The multi-part identifier "a.account_id" could not be bound.
Msg 4104, Level 16, State 1, Procedure pt_check_department, Line 18
The multi-part identifier "t.department" could not be bound.
Can someone explain what I am missing in this? Here is the trigger I am trying to run:
---Check for S_Org as the Department
--if yes, and account is not SHAKEY then give an error
=================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
CREATE TRIGGER pt_check_department
ON pt_time_project_tasks
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
selectt.department,a.account_id from
pt_time_project_tasks t inner join pt_time_projects a
on t.time_bill_id = a.time_bill_id
if
a.account_id <> '1562E82F-6852-DF11-A29C-00155D1E165E' and t.department = 'S_Org'
Begin
print 'S_Org can only be used if the ACCOUNT = SHAKEY'
ROLLBACK TRANSACTION
END
END
February 5, 2011 at 10:31 am
In select t.department,a.account_id from
pt_time_project_tasks t inner join pt_time_projects a
on t.time_bill_id = a.time_bill_id
if
a.account_id <> '1562E82F-6852-DF11-A29C-00155D1E165E' and t.department = 'S_Org'
Begin
the table aliases a and t are only defined in the select statement - they are not valid in the if statement that follows it. So they need to be replaced by the appropriate table names ("inserted" for "t", and "pt_time_projects" for "a").
But if you just change that you will get another (different error) because neither of the two things you are checking is syntactically constrained to be a single value, as opposed to a set of values - so you need to check for existence of a row which has the disallowed value combination: the if statement needs to be
IF EXISTS (select t..department,a.account_id
FROM inserted t INNER JOIN pt_time_projects a
ON t.time_bill_id = a.time_bill_id
WHERE a.account_id <> '1562E82F-6852-DF11-A29C-00155D1E165E'
AND t.department = 'S_Org'
)
BEGIN
And if you don't want your trigger to return a result set (a fairly unusual thing to want a trigger to do) you must get rid of the select statement you currently have before the if statement.
Tom
February 5, 2011 at 12:23 pm
Thank you much for the reply. I'll give it a try and let you know. (right now the network is down for upgrades so I'm busy waiting:)).
Thanks again,
Di-
February 5, 2011 at 6:17 pm
Perfect. The only thing I added was the use of Raiserror rather than print.
Thank you so very much!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply