Row wise operation in MSSQL Server

How can I create a row wise trigger in MSSQL Server .

CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...

For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!Yes, you could put a cursor in the trigger that operates on each row of the INSERTED table. But do yourself a favor and don't do it. Find a set-based solution using the INSERTED table instead.

Please describe what you are trying to do and why you think you need a row-wise operator (cursor). These are generally only needed by dynamic sql procedures or processes where the results of the operation on one record affects the results of the operation on the next record.|||AND...I'll add...anything that would require a cursor should not be done in a trigger...sql

Row triggers equivalent in sql server 2000

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things

if anyone can help i would appreciate a lot!

RegardsFad,

This should work for you:

create trigger MyTrigger on MyTable for insert, update
as
begin
update MyTable
set Modification = getdate()
from MyTable m
inner join inserted i on m.MyTableID = i.MyTableID
end

-- Bill

"Fad" <fadyay@.gmail.comwrote in message
news:1169163810.969473.16880@.m58g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things
>
if anyone can help i would appreciate a lot!
>
Regards
>

|||Thanks AlterEgo for the help!
now i get the functionality of inserted and deleted tablessql

Row level trigger

Hi,

Is there any row-level trigger available in SQL server(in oracle is does)

my problem is ..I am trying to write a trigger which updates all the items

But it give me error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

My statement is : SET @.var_item=(SELECT item_no FROM deleted)

How can i make it possible?
Note: I am trying convert Oracle trigger to SQL Server triggerWhy don't you post he Oracle Trigger...

inserted and deleted are called what again in Oracle?

oh yeah old and new...

It's not FOR EACH as in Oracle

Is there FOR ALL?

Don't know only got up to 8i...

But SQL Server is set based...|||Hi,
This oracle trigger and I want to translate in SQL SERVER

DECLARE
DB_ID NUMBER;
CQE NUMBER;
PC NUMBER;
item VARCHAR2(7);
AMT_PAID NUMBER(12,2);
AMT_RET NUMBER(12,2);
ITEM_NEW VARCHAR2(1);
quant number(12,3);
fiyr NUMBER;
BEGIN
/* Find the non null values to be used for propagating changes */
if :new.db_contract is not null then
db_id := :new.db_contract;
cqe := :new.cqe_numb;
pc := :new.pc_code;
item := :new.item_no;
fiyr := :new.fy_item;
else
db_id := :old.db_contract;
cqe := :old.cqe_numb;
pc := :old.pc_code;
item := :old.item_no;
fiyr := :old.fy_item;
end if;
amt_paid := nvl(:new.amt_paid_item,0) -nvl(:old.amt_paid_item,0);
amt_ret := nvl(:new.amt_ret_item,0) -nvl(:old.amt_ret_item,0);
quant := nvl(:new.quantity,0) -nvl(:old.quantity,0);
/* RAISE_APPLICATION_ERROR(-20501,'CHEK1 '||DB_ID||' CHEK2 '||PC
||' CHEK3 '||ITEM); */
SELECT NEW_ITEM INTO ITEM_NEW
FROM VALID_ITEM
WHERE DB_CONTRACT = DB_ID
AND PC_CODE = PC
AND ITEM_NO = ITEM;
update ae_contract
set amt_paid_contr = nvl(amt_paid_contr,0) + amt_paid,
amt_ret_contr = nvl(amt_ret_contr,0) + amt_ret
where db_contract = db_id;
if item_new = 'N' then
update vendor
set used_amt = nvl(used_amt,0) + amt_paid + amt_ret
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=db_id);
end if;
update enc_det
set amt_paid_fy = nvl(amt_paid_fy,0) + amt_paid,
amt_ret_fy = nvl(amt_ret_fy,0) + amt_ret
where db_contract = db_id
and pc_code = pc
and fy = fiyr;
update valid_item
set tamt_ret_item = nvl(tamt_ret_item,0) + amt_ret,
tamt_paid_item = nvl(tamt_paid_item,0) + amt_paid,
qtd = nvl(qtd,0) + quant
where db_contract = db_id
and pc_code = pc
and item_no = item;
end;|||Where's this stuff?

CREATE OR REPLACE TRIGGER hist_enr_dpnd_benef_tr
BEFORE DELETE or UPDATE on enr_dpnd_benef
FOR EACH ROW
IF DELETING THEN
INSERT into enr_dpnd_benef_h
VALUES(:old.EMPLID
,:old.DEPENDENT_BENEF
...

So you check to see if new is null and then assume it's a delete?

You need to join the virtual tables

Trigger syntax is...

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

And the interogation of the virtual tables tell you what happended..in this case, I'm checking for an update here...|||I translated in sql server this way...is it write?

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@.var_DB_contract INTEGER,
@.var_CQE INTEGER,
@.var_PC INTEGER,
@.var_item VARCHAR(7),
@.var_AMT_PAID INTEGER,
@.var_AMT_RET INTEGER,
@.var_ITEM_NEW VARCHAR(1),
@.var_quant DECIMAL,
@.var_fiyr INTEGER,
@.var_amt_result INTEGER,
@.var_amt_ret_result INTEGER,
@.var_amt_old INTEGER,
@.var_amt_ret_old INTEGER,
@.var_quant_result INTEGER,
@.var_quant_new INTEGER,
@.var_quant_old INTEGER,
@.Item_new VARCHAR(7),
@.var_chk varchar(1)

set @.var_db_contract =(Select db_contract from inserted)
IF @.var_db_contract IS NOT NULL
BEGIN
SET @.var_db_contract=(SELECT distinct db_contract FROM inserted)
SET @.var_cqe=(SELECT distinct cqe_numb FROM inserted)
-- SET @.var_pc=(SELECT pc_code FROM inserted)
-- SET @.var_item=(SELECT item_no FROM inserted)
-- SET @.var_fiyr=(SELECT fy_item FROM inserted)
set @.var_chk ="Y"
END
ELSE
BEGIN
SET @.var_db_contract=(SELECT distinct db_contract FROM deleted)
SET @.var_cqe=(SELECT distinct cqe_numb FROM deleted)
-- SET @.var_pc=(SELECT distinct pc_code FROM deleted)
--SET @.var_item=(SELECT item_no FROM deleted)
-- SET @.var_fiyr=(SELECT fy_item FROM deleted)
set @.var_chk="N"
END
SET @.var_amt_paid=(SELECT amt_paid_item FROM inserted)
SET @.var_amt_old=(SELECT amt_paid_item FROM deleted)
SET @.var_amt_result =ISNULL(@.var_amt_paid,0) - ISNULL(@.var_amt_old,0)

SET @.var_amt_ret = (SELECT amt_ret_item from inserted)
SET @.var_amt_ret_old=(SELECT amt_ret_item from deleted)
SET @.var_amt_ret_result = isnull(@.var_amt_ret,0) - isnull(@.var_amt_ret_old,0)

SET @.var_quant_new = (SELECT quantity from inserted)
SET @.var_quant_old =(SELECT quantity from deleted)
SET @.var_quant_result = isnull(@.var_quant_new,0) - isnull(@.var_quant_old,0)
if @.var_chk="Y"
begin
declare val_cur cursor for
select pc_code,item_no,fy_item from inserted
open val_cur
Fetch next from val_cur
into @.var_pc,@.var_item,@.var_fiyr
while @.@.fetch_status= 0
begin
SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_item
end
close val_cur
deallocate val_cur

end

else
begin
declare val_cur_del cursor for
select pc_code,item_no,fy_item from deleted
open val_cur_del
Fetch next from val_cur_del
into @.var_pc,@.var_item,@.var_fiyr
while @.@.fetch_status= 0
begin

SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_item
end
close val_cur_del
deallocate val_cur_del

end|||I thought I had mentioned it...

BUT...

You have to think in terms of SET Processing...

You can't just say SELECT @.x = col1 FROM inserted

It will give you the last row of data...

But SQL Server is trying to process ALL of the rows based on the event

So yo uneed to do a join to the virtual tables...|||Brett,

Can you give me any example of virtual binding based on my trigger?

I shall really appreciate.|||Here's a sample of one of mine...

It's for UPDATES AND DELETES..

The existance check see's which one it is...the first is for the updates..

Notice the join to the virtual table

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END|||Hi Brett,

Thank you very much for your cooperation. I changed the code and now I think it would work.

Any comments pleae let me know khuram@.hotmail.com

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@.var_DB_contract INTEGER,
@.var_CQE INTEGER,
@.var_PC INTEGER,
@.var_item VARCHAR(7),
@.var_AMT_PAID INTEGER,
@.var_AMT_RET INTEGER,
@.var_ITEM_NEW VARCHAR(1),
@.var_quant DECIMAL,
@.var_fiyr INTEGER,
@.var_amt_result INTEGER,
@.var_amt_ret_result INTEGER,
@.var_amt_old INTEGER,
@.var_amt_ret_old INTEGER,
@.var_quant_result INTEGER,
@.var_quant_new INTEGER,
@.var_quant_old INTEGER,
@.Item_new VARCHAR(7),
@.var_chk varchar(1)

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @.var_db_contract =(Select db_contract from inserted)
IF @.var_db_contract IS NOT NULL
BEGIN
SET @.var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @.var_chk ="Y"
END
ELSE
BEGIN
SET @.var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @.var_chk="N"
END
SET @.var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_amt_result =ISNULL(@.var_amt_paid,0) - ISNULL(@.var_amt_old,0)

SET @.var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_amt_ret_result = isnull(@.var_amt_ret,0) - isnull(@.var_amt_ret_old,0)

SET @.var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_quant_result = isnull(@.var_quant_new,0) - isnull(@.var_quant_old,0)
SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_item|||Nope you're still not there

This

set @.var_db_contract =(Select db_contract from inserted)

Won't work correctly

This should paint the picture for you...cut and paste it in QA

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
CREATE TABLE myTable00(Col1 int)
GO

CREATE TRIGGER myTriger99 ON myTable99 FOR INSERT
AS
DECLARE @.Col1 int
SELECT @.Col1 = Col1 FROM inserted
INSERT INTO myTable00(Col1) SELECT @.Col1
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Row level trigger

Hi all

I am doing a DB porting project in which i need to translate oracle specific queries to sql server.I need to convert following trigger to sqlserver specific .Any help will be appreciated

CREATE OR REPLACE TRIGGER T_BI_R_TARGET_OBJECTIVE
BEFORE INSERT
ON TARGET_OBJECTIVE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (NEW.TARGET_OBJECTIVE_ID IN (NULL,0))
DECLARE
seq_id NUMBER;
BEGIN
SELECT TARGET_OBJECTIVE_ID_SEQ.NEXTVAL INTO seq_id FROM dual;
:new.TARGET_OBJECTIVE_ID := seq_id;
END;

Regards
sreenathFirst of all read thru SQL books online for TRIGGERS topic which explains the information on SQL Server.

HTH

Row Level Trigger

How can i write a trigger which gets fired for each row that is being deleted

i.e. Row level TriggerI tried something like this

ALTER TRIGGER [dbo].[Del_Msg] ON [Seat27].[dbo].[Email_Messages] AFTER DELETE&......

it gets executed once for the triggering SQL statement

Oracle provides row and statement level triggers.

Sql Server does not. Instead, it provides statement level triggers with access to row-level information.

There are two dummy tables, @.inserted and @.deleted available inside your trigger.

If a record is in both tables, it was updated. Otherwise, it was inserted or deleted as obvious.

Your trigger needs to perform whatever row-oriented tasks it needs to do using the dummy tables for input.

|||Thanx

Row ID trigger

Anyone knows how to make a good trigger to create a row id on a table every time a new row is inserted? This is to maintain row id sequence by not setting the row id field as an auto identity which may be in broken sequence after a while.
Thanks.just use something like "select max(rowID)+1" in the update trigger. Although you'll have to careful of your isolation level.|||I tried what you said in INSERT trigger and it did'nt work.
Why?

Thanks.|||What's to know about isolation level?
Thanks.|||If you have a low isolation level then, potentially, two inserts would get the same result for select max()

What "doesn't work"?|||How can the low isolation level be taken care of?

Sorry, I tested the trigger in enterprise manager and at first didn't see the rowid appear until I refreshed the table, which made me believe the trigger wasn't working.

Thanks.|||"which may be in broken sequence after a while. "

Why do you think so, IMHO, it cannot be broken. If you don't like identity you can use
so-called "table with current id's for all tables" and update it through sps or udfs|||You need to have a quick read of the isolation levels. Basically the lower the level the less database locks you have - although that means you have more concurrency. The higher the level the more locks you have so the "safer" code like select max() becomes - but your concurrency drops. You can influence the isolation level using commands such as "SET ISOLATION LEVEL" or by using query hints. If you're using the select max() technique then I'd suggest you look at the UPDLOCK hint.|||Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.
Thanks.|||>> Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.

How are you trying to use a UDF? They're just a way of batching SQL, they still need to be called...like a stored procedure. So you'd still need to put them in a trigger.

>> how can they get out of sequence
Easily! Insert, rollback, Insert. Where can I buy a MCSDBA for $2 please?