Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Angry Unanswered: Create trigger which wil do event before delete a record..

    Hi There,
    I have created a trigger which supposingly will do event before delete the record from its own table.
    unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be.

    the trigger is as below :
    =======================
    CREATE TRIGGER TG_D_AGENT ON dbo.Agent
    FOR DELETE
    AS
    begin
    declare @vAgentID as numeric,
    @vAgency as varchar(50),
    @vUnit as varchar(50),
    @vAgentCode as varchar(50),
    @vName as varchar(50),
    @vIC as varchar(14),
    @vAddress as varchar(100),
    @vContactNumber as varchar(50),
    @vDownlink as varchar(50),
    @vGSM as varchar(10),
    @vAM as varchar(10),
    @vDeleted_date as datetime

    set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)

    declare cur_policy_rec CURSOR for
    select AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from inserted

    open cur_policy_rec
    fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM

    WHILE @@FETCH_STATUS=0
    BEGIN

    INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date)
    VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)

    fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM

    end

    deallocate cur_policy_rec
    end
    ===============================

    in oracle , i normallly can do something like this...
    ====================================
    CREATE TRIGGER TG_D_AGENT ON dbo.Agent
    BEFORE DELETE ON dbo.Agent FOR EACH ROW
    begin

    Is that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  2. #2
    Join Date
    Apr 2007
    Posts
    29
    hehe sorry, folk, i gues i found the way! i shld be using
    deleted insert of inserted!
    god bless all

    adios
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

Posting Permissions

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