Error on Returning More than 1 Value

[yanksRoll]

Hi,

I'm having a problem with the following trigger:

CREATE TRIGGER tr_FamID
ON dbo.myFirstTbl
FOR INSERT
AS
DECLARE @address2 VARCHAR (50)
DECLARE @compareFamID INT

SELECT @address2 = Address FROM inserted
SELECT @compareFamID = FamID FROM inserted

IF @address2 = ' '
BEGIN
DROP TRIGGER tr_famID
END

IF @address2 != ' '
BEGIN
DECLARE @newRec int
DECLARE @newFam int
DECLARE @newLast VARCHAR(50)
DECLARE @newFirst VARCHAR(50)
DECLARE @newAddress VARCHAR(50)

SET @newRec = (SELECT RecID FROM myFirstTbl)
SET @newFam = (SELECT FamID FROM myFirstTbl)
SET @newLast = (SELECT LastName FROM myFirstTbl)
SET @newFirst = (SELECT FirstName FROM myFirstTbl)
SET @newAddress = (SELECT Address FROM myFirstTbl)

INSERT INTO dbName.dbo.mySecondTbl (FamID, LastName,
FirstName, Address) SELECT @newFam, @newLast, @newFirst,
@newAddress FROM myFirstTbl WHERE @newFam = @compareFamID

DELETE FROM myFirstTbl WHERE FamID = @compareFamID
END

The error that's giving me is that the subquery is returning more than one value...and I guess sql doesn't like that. What I want is to INSERT all of the records on the myFirstTbl table that have the same FamID (for example...WHERE FamID = '2') into the mySecondTbl table which is located in another database separate from the myFirstTbl table. After that's done, I would like to delete the records from the myFirstTbl table. So, because of this, I think it makes sense for the subquery to return more than 1 value, but it's erroring out. The information will be entered into the database using a Visual Basic.Net application (Visual Studio 2005). I would also like to add that i'm new to triggers so if i'm doing something that I should't be doing or that could hurt performance, please tell me.

I would really appreciate your help.

[azimmer]

Hi,
...
INSERT INTO dbName.dbo.mySecondTbl (FamID, LastName,
FirstName, Address) SELECT @newFam, @newLast, @newFirst,
@newAddress FROM myFirstTbl WHERE @newFam = @compareFamID
...

The error that's giving me is that the subquery is returning more than one value...and I guess sql doesn't like that.

...

I think the problem is that the inner SELECT is a bit malformed. I believe you meant to select columns from myFirstTable in which case you column names of myFirstTable in the select list not local variables (i.e. surely no @'s in it). It also applies to the left part of the WHERE clause, like this:

NSERT INTO dbName.dbo.mySecondTbl (FamID, LastName,
FirstName, Address) SELECT myFirstTbl.FamID, myFirstTbl.LastName,
myFirstTbl.FirstName, myFirstTbl.Address FROM myFirstTbl WHERE myFirstTbl.FamID = @compareFamID
 

It is also an interesting action to drop the trigger from within the trigger. While to my recon it is OK for the SQL Server (syntax OK), I wouldn't use it myself. (The trigger may mysteriuosly disappear with an insert... If you do want to get rid of it, do it explicitly from the application code.)

[yanksRoll]

thanks for your help man, it was very helpful. You were right, I didn't needed the variables. It worked by replacing the variables with the actual column names from the table.

As for DROPING the trigger, you were right again...it was a bad idea to include it.

[ck9663]

thanks for your help man, it was very helpful. You were right, I didn't needed the variables. It worked by replacing the variables with the actual column names from the table.

As for DROPING the trigger, you were right again...it was a bad idea to include it.

or access the inserted table instead...trigger runs right after the insert, but it will not immediately commit the transactions...it will insert a similar record first on the inserted table, before it actually insert it to your myFirstTable...that's the best way to access the record that you are trying to insert..