The trigger worked in SQL Server 7.0!

[jobi]

This was an OK statement in SQL Server 7.0:

CREATE TRIGGER [TriggerNm] ON [Table1]
FOR INSERT, UPADATE
AS
UPDATE [Table1]
SET Table.Col1=(SELECT DISTINCT ColA FROM Table2
WHERE Table1.Col2=Table2.ColB),
Col3 = (SELECT DISTINCT ColX from Table3
WHERE Table1.Col4=Table3.ColY)

In SQL Server 2000, I get an error on the comma (,) at the end of line 6.
How do I write this in SQL Server 2000?

 

[Lekar]

After the Set statement, you wrote Table, when it is Table1, but I don't know if that is the error. Tell us if this works.

 

[swampBoogie]

CREATE TRIGGER [TriggerNm] ON [Table1]
FOR INSERT, UPADATE
AS
UPDATE Table1
SET Table1.Col1=(SELECT DISTINCT ColA 
FROM Table2
WHERE Table1.Col2=Table2.ColB and 
Col3 in (SELECT ColX from Table3
WHERE Table1.Col4=Table3.ColY)

 

[jobi]

I don't think this is what I want.

In SQL Server 7.0, when i use comma (,) I start a new set command.
In my example, I set values to Table1.Col1 AND Col3. I update both fields in one trigger. In SQL Server 2000 the comma returns an error.

(Lekar: This is not the error. A misspell in this example. Sorry!)

 

[PHV]

Have you tried something like this ?
CREATE TRIGGER [TriggerNm] ON [Table1]
FOR INSERT, UPDATE
AS
UPDATE [Table1]
SET (Col1,Col3)=(
(SELECT DISTINCT ColA FROM Table2
WHERE Table1.Col2=Table2.ColB),
(SELECT DISTINCT ColX from Table3
WHERE Table1.Col4=Table3.ColY)
)

Hope This Help
PH.

 

[jobi]

I still get an error:
Error 170: Line 5: Incorrect syntaxnear '('.

 

[nigelrivett]

A few things about the trigger
It will update all rows of table1 not just those being inserted/updated - is that what you want?.
The subqueries need to return just one value per row so distinct is maybe not a good choice.
You need to set the value in the same table as you are updating (not Table and Table1
Saying that the error you are getting doesn't refer to any of this - I suspect you have an unprintable character.


UPDATE [Table1]
SET Col1=(SELECT top 1 ColA FROM Table2 WHERE Table1.Col2=Table2.ColB) ,
Col3 = (SELECT top 1 ColX from Table3 WHERE Table1.Col4=Table3.ColY)


n.b.
UPDATE [Table1]
SET (Col1,Col3)=(
isn't valid in sql server.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.