@@Identity being over-written by Insert Trigger in stored procedure.

Hi All

I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

I have been using the @.@.Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an after insert Trigger has been added to the table being updated.

Now the @.@.Identity is returning the identity value of the trigger that was called instead of the original table insert.

Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

Thank in advance.

Eamon.Look at SCOPE_IDENTITY and/or IDENT_CURRENT

Regards,

hmscott|||Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!

@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

@@ identity insert

Is it possible to create a trigger that inserts the @.@.identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-yes it's possible. Create a trigger on Table1 for insert and execute an insert-statement using @.@.identity. Are you stuck?

@ and @@ in a trigger

Hi,
I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
are?
Thanks for help.
Jason
@. indicates a local variable or parameter.
@.@. indicates a class of parameter-less system functions, such as @.@.rowcount,
which returns the number or rows affected by the most recent command.
You can read about local variables and parameters in the Books Online.
If you go to the BOL index, you will see all the @.@. functions listed
together, and you can see what they all are.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>
|||Thanks Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:u84AT6QOGHA.964@.tk2msftngp13.phx.gb l...
> @. indicates a local variable or parameter.
> @.@. indicates a class of parameter-less system functions, such as
> @.@.rowcount, which returns the number or rows affected by the most recent
> command.
> You can read about local variables and parameters in the Books Online.
> If you go to the BOL index, you will see all the @.@. functions listed
> together, and you can see what they all are.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>
>
|||Jason Huang a crit :
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>
If you see thoses characters in a trigger code, the is a good chance
that this trigger wont work properly...
Why ?
Because a trigger id fired only once even if the SQL statement that fire
it, had to update, insert or delete on million row.
So variables to get some datas in pseudo tables (inserted, deleted) ...
will only works for one row of the set...
Wich mean that this trigger will not produce its effects on n-1 other
rows...
A +
PS : sorry for my bad english
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||I don't agree.
It is EXTREMELY common to check @.@.rowcount as soon as the trigger starts, to
verify how many rows were affected, and if it is more than 1, then we can
rollback or process the data in a different way. And since @.@.rowcount
changes every time you look at it, you may want to capture it into your own
@. variable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23iXktcROGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Jason Huang a crit :
> If you see thoses characters in a trigger code, the is a good chance that
> this trigger wont work properly...
> Why ?
> Because a trigger id fired only once even if the SQL statement that fire
> it, had to update, insert or delete on million row.
> So variables to get some datas in pseudo tables (inserted, deleted) ...
> will only works for one row of the set...
> Wich mean that this trigger will not produce its effects on n-1 other
> rows...
> A +
> PS : sorry for my bad english
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

@ and @@ in a trigger

Hi,
I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
are?
Thanks for help.
Jason@. indicates a local variable or parameter.
@.@. indicates a class of parameter-less system functions, such as @.@.rowcount,
which returns the number or rows affected by the most recent command.
You can read about local variables and parameters in the Books Online.
If you go to the BOL index, you will see all the @.@. functions listed
together, and you can see what they all are.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>|||Thanks Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:u84AT6QOGHA.964@.tk2msftngp13.phx.gbl...
> @. indicates a local variable or parameter.
> @.@. indicates a class of parameter-less system functions, such as
> @.@.rowcount, which returns the number or rows affected by the most recent
> command.
> You can read about local variables and parameters in the Books Online.
> If you go to the BOL index, you will see all the @.@. functions listed
> together, and you can see what they all are.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
>> are?
>> Thanks for help.
>>
>> Jason
>>
>
>|||Jason Huang a écrit :
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>
If you see thoses characters in a trigger code, the is a good chance
that this trigger wont work properly...
Why ?
Because a trigger id fired only once even if the SQL statement that fire
it, had to update, insert or delete on million row.
So variables to get some datas in pseudo tables (inserted, deleted) ...
will only works for one row of the set...
Wich mean that this trigger will not produce its effects on n-1 other
rows...
A +
PS : sorry for my bad english
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree.
It is EXTREMELY common to check @.@.rowcount as soon as the trigger starts, to
verify how many rows were affected, and if it is more than 1, then we can
rollback or process the data in a different way. And since @.@.rowcount
changes every time you look at it, you may want to capture it into your own
@. variable.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23iXktcROGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Jason Huang a écrit :
>> Hi,
>> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
>> are?
>> Thanks for help.
>>
>> Jason
> If you see thoses characters in a trigger code, the is a good chance that
> this trigger wont work properly...
> Why ?
> Because a trigger id fired only once even if the SQL statement that fire
> it, had to update, insert or delete on million row.
> So variables to get some datas in pseudo tables (inserted, deleted) ...
> will only works for one row of the set...
> Wich mean that this trigger will not produce its effects on n-1 other
> rows...
> A +
> PS : sorry for my bad english
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

@ and @@ in a trigger

Hi,
I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
are?
Thanks for help.
Jason@. indicates a local variable or parameter.
@.@. indicates a class of parameter-less system functions, such as @.@.rowcount,
which returns the number or rows affected by the most recent command.
You can read about local variables and parameters in the Books Online.
If you go to the BOL index, you will see all the @.@. functions listed
together, and you can see what they all are.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>|||Thanks Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:u84AT6QOGHA.964@.tk2msftngp13.
phx.gbl...
> @. indicates a local variable or parameter.
> @.@. indicates a class of parameter-less system functions, such as
> @.@.rowcount, which returns the number or rows affected by the most recent
> command.
> You can read about local variables and parameters in the Books Online.
> If you go to the BOL index, you will see all the @.@. functions listed
> together, and you can see what they all are.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>
>|||Jason Huang a crit :
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>
If you see thoses characters in a trigger code, the is a good chance
that this trigger wont work properly...
Why ?
Because a trigger id fired only once even if the SQL statement that fire
it, had to update, insert or delete on million row.
So variables to get some datas in pseudo tables (inserted, deleted) ...
will only works for one row of the set...
Wich mean that this trigger will not produce its effects on n-1 other
rows...
A +
PS : sorry for my bad english
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree.
It is EXTREMELY common to check @.@.rowcount as soon as the trigger starts, to
verify how many rows were affected, and if it is more than 1, then we can
rollback or process the data in a different way. And since @.@.rowcount
changes every time you look at it, you may want to capture it into your own
@. variable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23iXktcROGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Jason Huang a crit :
> If you see thoses characters in a trigger code, the is a good chance that
> this trigger wont work properly...
> Why ?
> Because a trigger id fired only once even if the SQL statement that fire
> it, had to update, insert or delete on million row.
> So variables to get some datas in pseudo tables (inserted, deleted) ...
> will only works for one row of the set...
> Wich mean that this trigger will not produce its effects on n-1 other
> rows...
> A +
> PS : sorry for my bad english
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

:new.<variable_name>

I am trying to write an 'after insert' trigger for a very dynamic database that inserts all the new values put into a table into an audit table. To do this, I have to write :new.col1, :new.col2, :new.col3 etc into the audit table. I have a loop that puts ['col1','col2','col3', etc.] into a variable named column_name, but I don't seem to be able to dynamically generate the variable name. I guess I am looking for something like an eval function in PL/SQL that could do

stmt := 'new_value := :new.'||column_name;
eval(stmt);
insert into audit_table (:new.id,column_name,new_value,SYSDATE);

But I haven't found it yet, and I can't select column_name from table_name because table 'table_name' is mutating and Oracle won't let you select on a table that is changing. If anyone can help, it would be greatly appreciated. Thanks in advance.Hello,

I dont know such a command in PL/SQL ... but what do you think about dynamic SQL. Use the package methods DMBS_SQL.

I hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Hey, I can use dynamic SQL for something like the following

sql_stmt = 'select :col_name from :table_name where id=:id';
execute immediate into new_value using col_name, table_name, id

But this is an actual PL/SQL statement that I need to build on the fly like this

stmt := 'new_value=:new.'||col_name;
eval(stmt);
insert into audit_table values(:new.id,new_value,SYSDATE,col_name);

Any thoughts would be greatly appreciated. Thanks.|||Hello,

whats about

DECLARE
cVar VARCHAR2(500) := 0;
BEGIN
cVar := 'DECLARE ' ||
' cThis VARCHAR2(200) := ''' ||
'BEGIN ' ||
' cThis := :new.' || column_name ||
' INSERT INTO and so on and so on ' ||
'END;';

.
.
.
do the dynamic stuff
.
.
.

END;

Is this what you want to do ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

**HAVE NULL AND NOT DUBLICATED VALUES IN COL**

In my opinon the best way of doing this would be through
an 'INSTEAD OF INSERT' trigger.
If you give a bit more on the requirements I can help with
the code, for instance can you have two of...
105,'Tom',null
105,'Tom',null
In the table ?
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>Hi
>I'm using SQL 2000,how is it possible to control a value
of column to get
>the ollowing result?
>Emp (EmpId PK,EmpName,EmpCode)
>EmpId EmpName EmpCode
>-- -- --
>100 John 3
>101 Anne 5
>102 Robert null
>103 Jennifer null
>104 Peter 78
>I want to have some NULLs or a unique number in EmpCode
column as above,
>I tried to define Uniqe index or constraint but it wasn't
successfull
>because of null,
>now how can I prevent of inserting a record of
(105,'Tom',3) and not prevent
>of inserting a record like (105,'Tom',null)
>Any help would be greatly thankful.
>
>.
>No, we can't have
> 105,'Tom',null
> 105,'Tom',null
cause the first column is a PK,
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:204701c5141e$26f85330$a501280a@.phx.gbl...
> In my opinon the best way of doing this would be through
> an 'INSTEAD OF INSERT' trigger.
> If you give a bit more on the requirements I can help with
> the code, for instance can you have two of...
> 105,'Tom',null
> 105,'Tom',null
> In the table ?
> Peter
> "Choose a job you love, and you will never have to work a
> day in your life."
> Confucius
>
> of column to get
> column as above,
> successfull
> (105,'Tom',3) and not prevent

***Update Trigger ***

Hi Group Members
Description of table ALPHA
col1int
col2int
col3int
Description of table BETA
tar1int
tar2int
tar3int
I would like create an update trigger for any update on ALPHA table
it should be reflected rightaway on BETA table.
Thanks and good luck
I'm assuming that col1 & tar1 are FK with a 1-to-1, and col2/3 & tar2/3
are the columns you want to carry the updates to - but something like
this should work:
CREATE TRIGGER utr_AlphaBeta ON ALPHA
FOR UPDATE
AS
BEGIN
UPDATEBETA
SETBETA.tar2 = INSERTED.col2,
BETA.tar3 = INSERTED.col3
FROMINSERTED,BETA
WHEREINSERTED.col1 = BETA.tar1
END
.....and good luck to you.
kevincristo@.gmail.com wrote:
> Hi Group Members
>
> Description of table ALPHA
> col1int
> col2int
> col3int
>
> Description of table BETA
> tar1int
> tar2int
> tar3int
> I would like create an update trigger for any update on ALPHA table
> it should be reflected rightaway on BETA table.
> Thanks and good luck
|||On 23 Mar 2006 12:49:37 -0800, kevincristo@.gmail.com wrote:

>Hi Group Members
>
>Description of table ALPHA
>col1int
>col2int
>col3int
>
>Description of table BETA
>tar1int
>tar2int
>tar3int
>I would like create an update trigger for any update on ALPHA table
>it should be reflected rightaway on BETA table.
>Thanks and good luck
Hi kevincristo,
Best solution, IMO:
DROP TABLE Beta
go
CREATE VIEW Beta
AS
SELECT col1 AS tar1, col2 AS tar2, col33 AS tar3
FROM Alpha
go
Hugo Kornelis, SQL Server MVP

***Update Trigger ***

Hi Group Members
Description of table ALPHA
col1 int
col2 int
col3 int
Description of table BETA
tar1 int
tar2 int
tar3 int
I would like create an update trigger for any update on ALPHA table
it should be reflected rightaway on BETA table.
Thanks and good luckI'm assuming that col1 & tar1 are FK with a 1-to-1, and col2/3 & tar2/3
are the columns you want to carry the updates to - but something like
this should work:
CREATE TRIGGER utr_AlphaBeta ON ALPHA
FOR UPDATE
AS
BEGIN
UPDATE BETA
SET BETA.tar2 = INSERTED.col2,
BETA.tar3 = INSERTED.col3
FROM INSERTED,BETA
WHERE INSERTED.col1 = BETA.tar1
END
....and good luck to you.
kevincristo@.gmail.com wrote:
> Hi Group Members
>
> Description of table ALPHA
> col1 int
> col2 int
> col3 int
>
> Description of table BETA
> tar1 int
> tar2 int
> tar3 int
> I would like create an update trigger for any update on ALPHA table
> it should be reflected rightaway on BETA table.
> Thanks and good luck|||On 23 Mar 2006 12:49:37 -0800, kevincristo@.gmail.com wrote:

>Hi Group Members
>
>Description of table ALPHA
>col1 int
>col2 int
>col3 int
>
>Description of table BETA
>tar1 int
>tar2 int
>tar3 int
>I would like create an update trigger for any update on ALPHA table
>it should be reflected rightaway on BETA table.
>Thanks and good luck
Hi kevincristo,
Best solution, IMO:
DROP TABLE Beta
go
CREATE VIEW Beta
AS
SELECT col1 AS tar1, col2 AS tar2, col33 AS tar3
FROM Alpha
go
Hugo Kornelis, SQL Server MVP

(SQL Server 2005) Instead of Delete Trigger

Hi,

I simply need a trigger to delete some datasets from a view but have some trouble writing an adequate trigger. Here is my attempt:

Use myDB;
Go
CREATE TRIGGER IO_Trig_Del_myView ON myView
INSTEAD OF Delete
AS
BEGIN
SET NOCOUNT ON
-- Check for dataset, if present delete.
IF (EXISTS (SELECT Z.[myPk]
FROM myTable t, deleted
WHERE t.[myPk] = deleted.[myPk]))
Delete From myTable
Where myTable.[myPk] = deleted.[myPk]...

This causes the following failure:

Msg 4104, Level 16, State 1, Procedure IO_Trig_Del_myView, Line 11
The multi-part identifier "deleted.myPK" could not be bound.

Can somebody explain the reason to me? myPk is part of the View I created. Since I do have three tables in myView so I get that message three times, once per table.

Hi...

The deleted object is a Table and you have to treat it as such. That means it can contain more then one row of data so you have to take this into account in your querry...

You could try the following querry

Delete From myTable
Where myTable.[myPk] in ( select [myPk] from deleted) ...

|||

Thanks.

Although I don't understand it. Isn't it possible to select more than one row via the = operator? I mean if I take a join for example I compare via = and can get more than one row and also compare more rows.

|||

The = can only be used to compare one Value against another one. To compare one Value against a set of other values you need the "in" clause. The syntax of a join is a little different here. In fact it could also be rewritten as a join to achieve the same result by my preference to doing delete statements is to first write the querry that will show me what to delete and then encapsulate that querry inside the In clause (Thats just a personal preference)

|||

Check out this delete trigger. This will do what you want, but you have to include the deleted table in the trigger (I am using a correlated subquery, but a join would work to)

create TABLE test
(
myPk int PRIMARY key
)
go
CREATE VIEW myView
AS
SELECT myPk
FROM test
go

CREATE TRIGGER IO_Trig_Del_myView ON myView
INSTEAD OF Delete
AS
BEGIN
SET NOCOUNT ON

-- Check for dataset, if present delete. (do this in one step
DELETE FROM myTable
WHERE EXISTS (SELECT *
FROM deleted
WHERE myTable.myPk = deleted.myPk)
END
go

--Code to test with...

INSERT INTO test
VALUES (1)
INSERT INTO test
VALUES (2)
INSERT INTO test
VALUES (3)
INSERT INTO test
VALUES (4)
INSERT INTO test
VALUES (5)

SELECT * FROM test
DELETE FROM myView WHERE myPk = 1
SELECT * FROM test
DELETE FROM myView WHERE myPk IN (2,4)
SELECT * FROM test