March 23, 2004 at 1:58 pm
Hi All,
I'm getting an error I don't understand in attempting to write a trigger for insets, updates, and deletes. I'm writing it on a copy of the Northwind database that has no DRI of any kind. The EmployeeID column is an identity column however.
I've searched the the KB and Google and find the error messages in relation to inserts in SQL 7 with the NOBROWSETABLE and INSERT EXEC in the statement. However, I've been unable to find anything in relation to SQL Server 2000 that tells me why I should be getting the errors I'm getting. However the problem was supposed to have been fixed in SP2 in SQL 7.
As I'm pretty much a newbie at this I need to know if this is a known bug or if this is my error in coding this.
The code for the trigger follows, and after the code the errors from Query Analyzer.
Thanks in advance,
Freddy
Create Trigger EmployeeEmployeeTerritories
on Employees1
For Insert, Update, Delete
AS
Declare @Count int
Select @Count = Count(*) from Deleted
If @Count > 0
Begin
Delete from EmployeeTerritories1
From Deleted d
Join EmployeeTerritories1 ET
On d.EmployeeID = ET.EmployeeID
End
If @@Error !=0
RollBack Tran
Select @Count = Count(*) from Inserted
If @Count > 0
Begin
Insert Into EmployeeTerritories1
Select *
From Inserted i
left Join EmployeeTerritories1 ET
On i.EmployeeID = ET.EmployeeID
Where ET.EmployeeID Is Null
End
IF @@Error !=0
RollBack Tran
Here are the errors:
/*-----------------------------
Create Trigger EmployeeEmployeeTerritories
on Employees
For Insert, Update, Delete
-----------------------------*/
Server: Msg 213, Level 16, State 5, Procedure EmployeeEmployeeTerritories, Line 25
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 311, Level 16, State 1, Procedure EmployeeEmployeeTerritories, Line 25
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Server: Msg 311, Level 16, State 1, Procedure EmployeeEmployeeTerritories, Line 25
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
March 23, 2004 at 3:41 pm
Based on the error it is barking about this
Insert Into EmployeeTerritories1
Select *
From Inserted i
left Join EmployeeTerritories1 ET
On i.EmployeeID = ET.EmployeeID
Where ET.EmployeeID Is Null
Do both EmployeeTerritories1 and Employees1 have the same structure as far as columns. If so may be a text, ntext or image column throwing the error you need to come up with a way to add outside the trigger. If not thou you need to do something like
INSERT EmployeeTerritories1 (col1, col2 col3)
SELECT col1, col2 col3 FROM inserted i
left Join EmployeeTerritories1 ET
On i.EmployeeID = ET.EmployeeID
Where ET.EmployeeID Is Null
March 23, 2004 at 4:08 pm
Antares,
Thanks. You diagnosed it very accurately.
LOL. Being a newbie in anything is just so much fun. The mistake was just my not thinking through real thoroughly how I was going to hack an existing script to accomplish a similar, but not identical task. The original script was for identical tables and I was adapting it to use with dissimilar tables.
Thanks again. I appreciate the help.
March 23, 2004 at 5:59 pm
Quite alright. Enjoy posting frequenetly as needed and see if you can help others solve their issues also, best way to learn anything is to find a problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply