Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy Unanswered: 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 trigger
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why 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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Oracle Trigger...needs to translat in SQL Server

    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 := ld.db_contract;
    cqe := ld.cqe_numb;
    pc := ld.pc_code;
    item := ld.item_no;
    fiyr := ld.fy_item;
    end if;
    amt_paid := nvl(:new.amt_paid_item,0) -nvl(ld.amt_paid_item,0);
    amt_ret := nvl(:new.amt_ret_item,0) -nvl(ld.amt_ret_item,0);
    quant := nvl(:new.quantity,0) -nvl(ld.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;
    mr_roomi

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's this stuff?

    Code:
    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...

    Code:
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    SQL Server Trigger..row level

    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
    mr_roomi

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Virtual Join of tables

    Brett,

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

    I shall really appreciate.
    mr_roomi

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    I think it would work now

    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
    mr_roomi

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope you're still not there

    This

    Code:
    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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •