Thursday, March 22, 2012

Delete * Help??

Below is my Trigger. I want to DELETE ALL rows from EmployeeTemp but I receive a syntax error when doing a DELETE * FROM......
ANY IDEAS???

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Trigger trg_employees
On dbo.Employees
For Update
AS

Declare @.LastName varchar(255)
Declare @.FirstName varchar(255)
Declare @.Address varchar(255)
Declare @.City varchar(255)
Declare @.EmployeeID int

BEGIN
set @.LastName = (select LastName from Inserted)
set @.FirstName = (select FirstName from Inserted)
set @.Address = (select Address from Inserted)
set @.City = (select City from Inserted)
set @.EmployeeID = (select EmployeeID from Inserted)

Delete * EmployeeTEMP ----CAUSES SYNTAX ERROR BECAUSE OF '*'

INSERT INTO EmployeeTemp(EmployeeID, LastName, FirstName, Address, City)
Values(@.EmployeeID, @.LastName, @.FirstName, @.Address, @.City)
END
GO

EXEC master..xp_startmail
EXEC master..xp_sendmail
@.recipients ='grueneic@.drtel.com',
@.subject = 'Closed Service Order',
@.message = 'message test',
@.query = 'select EmployeeID, FirstName, LastName, Address, City from Northwind.dbo.EmployeeTemp'
EXEC master..xp_stopmail

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOIt would be

Delete * from TableName

but! if you TRUNCATE Table TableName
its _alot_ faster because it is a non-logged operation.|||Also be carefule in how you write your trigger. What happens if someone updates three rows instead of one row?

After you delete/truncate the table you could:

INSERT INTO EmployeeTemp(EmployeeID, LastName, FirstName, Address, City)
select EmployeeID, LastName, FirstName, Address, City
from inserted|||I agree with Paul Young. Your trigger is a bit unsafe.

But for the DELETE transaction, just write it that way:

DELETE TableName

No comments:

Post a Comment