asked on

help with stored procedure?

Hi,

Can you help with me this please?
I would like to know how to delete everything from this select statement and also insert them into another table.

if exists(Select empID FROM tblAvailableEmployee)
BEGIN
select a.AvalBusID from tblAvailableBus a inner join tblAvailableEmployee b on a.empID=b.empID

//insert them into a table called tblOutBus
then delete those records from tblAvailableBus
END

ASKER CERTIFIED SOLUTION

SJCFL-Admin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

keyu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SJCFL-Admin

I was thinking about this on the way in to work. And I think of the capturing of the deletes more as an application audit. And as I am not fond of allowing the foxes to guard the hen house, audits are the one function that I really prefer implementing withen databse triggers. that way, they fire even if someone executes an ad-hoc query.

Somethinig fairly simple like..

Create trigger [dbo].[TrgDeletetblAvailableBus

]
on [dbo].[tblAvailableBus]
for DELETE
as

insert into tblOutBus(AvalBusID)
select AvalBusID from deleted
GO

But I would add standard audit attributes to the table and my audit table would include an identity , insert datetime, user. It would look more like..

Create trigger [dbo].[TrgDeletetblAvailableBus

]
on [dbo].[tblAvailableBus]
for DELETE
as

insert into tblOutBus(AvalBusID,Deleted,DeletedBy)
select AvalBusID,Getdate(),SYSTEM_USER from deleted
GO

This covers cases 1 and 3

SJCFL-Admin

You can create a trigger on DROP table, but it is an AFTER trigger, so the table is already dropped when it fires. To audit the data in the table before the drop, you would put a rollback in the trigger, do the inserts to the audit table directly from the source table and the redrop the table. I think it is better not to allow anyone to drop the table. A DELETE FROM will clear the table and allow the trigger to fire with less overhead than a drop with the rollback. (my opinion - have not benchmarked...)