Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Stored Procedure

    Hi,

    I am having 2 tables
    1)Drug_details
    2)Expiry_details

    In drug details I am having the fields such as
    drug_id, batch_no, expiry_date and status.
    Here i need to update the status as 'Expired',
    if the expiry_date is less than or equal to current date.
    As well as if the Status is updated as 'Expired', i need to delete the
    entire row from drug_details table and the same row should be inserted into Expiry_details table.

    How it could be implemented. Pls get me some idea.


    Thank You.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    UPDATE Drug_details
    SET status = 'Expired'
    WHERE expiry_date <= GetDate()
    
    INSERT INTO Expiry_details(drug_id, batch_no, expiry_date)
    SELECT drug_id, batch_no, expiry_date
    FROM Drug_details
    WHERE status = 'Expired'
    
    DELETE 
    FROM Drug_details
    WHERE status = 'Expired'
    Don't forget to enclose it in a transaction.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    here is a solution

    I m sure you are trying this thng where data are already there in tables and your requirement is based on the condition where you hit / call procedure everytime.

    1) create a deleted trigger in your "drug" table. when the data get deleted from the "drug" table, they get inserted into the expiry table.

    2)write a procedure for calling your "drug" table where update and delete scripts should be written.

  4. #4
    Join Date
    Sep 2010
    Posts
    153
    Here is your trigger :-


    CREATE trigger drug_deletion on drug after delete
    as
    declare @drug_id nvarchar(10);
    declare @batch_no nvarchar(10);
    declare @expiry_date datetime;
    declare @status nvarchar(10);

    select @drug_id = d.drug_id from deleted d;
    select @batch_no = d.batch_no from deleted d;
    select @expiry_date = d.expiry_date from deleted d;
    select @status = d.status from deleted d;

    insert into expiry(
    drug_id,
    batch_no,
    expiry_date,
    status)
    values
    (@drug_id,
    @batch_no,
    @expiry_date,
    @status)

  5. #5
    Join Date
    Sep 2010
    Posts
    153
    Here is your procedure :-

    CREATE procedure [dbo].[expiry_insertion2]
    as
    declare @drug_id nvarchar(10);
    declare @batch_no nvarchar(10);
    declare @expiry_date datetime;
    declare @status nvarchar(10);

    begin
    update drug set status = 'expired' where expiry_date <= getdate();
    end;

    begin
    delete from drug where status = 'expired';
    end;

  6. #6
    Join Date
    Sep 2010
    Posts
    153
    Call the procedure. you will get your result.

    drug:-

    drug_id batch_no expiry_date status..
    -------- ------------- ------------- ------------

    same fields are there in expiry table.

  7. #7
    Join Date
    Sep 2010
    Posts
    153

    shakthipriya

    ebdi irkinge? :P


    as far as i know, i had told you somewhere about the time based trigger. We cannot schedule time on trigger but i meant that you can create a batch file in windows scheduler and schedule time on it or else you can check out the concept of agent job in ms sql server.

Posting Permissions

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