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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment