Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    9

    Unanswered: 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
    GO

  2. #2
    Join Date
    May 2002
    Posts
    25
    It would be

    Delete * from TableName

    but! if you TRUNCATE Table TableName
    its _alot_ faster because it is a non-logged operation.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    I agree with Paul Young. Your trigger is a bit unsafe.

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

    DELETE TableName
    Data Climber

Posting Permissions

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