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