Delete data before trigger executes

I am trying to delete data from a table prior to populating with new data via a trigger if certain critria matches to eliminate duplicates. I have copied the trigger below. The syntax checks ok but I get any error message saying 'Incorrect syntax near 'GO'. 'ALTER TRIGGER' must be the first statement in a query batch' when I try to save.

Can someone tell me if this is possible please.

IF EXISTS (SELECT * FROM hold_complete
WHERE fkey = hold_complete.fkey AND actiontext = 'hold' and Subactiontext = 'pending user')
delete from hold_complete where hold_complete.fkey = fkey
GO
CREATE TRIGGER tr_hold_complete ON CallsHistory
for INSERT AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')Does the trigger tr_hold_complete already exist?|||Yes it does|||if it already exist you can't create a trigger with the same name. If you use alter trigger (and the same code), it will modify the trigger without changing anything and your code should work. Not sure if there might be a nicer implementation though|||There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.

Can that be done in a single trigger ?|||I think i see the error but a correct implementation eludes me at the moment and I have no access to books online or a sql server to check.

From the error i would guess that you can't have the syntax before the go statement when either creating or altering the trigger.

Not sure how to get round this one sorry.|||Originally posted by Bracksboy
There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.

Can that be done in a single trigger ?

read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.|||Originally posted by Enigma
read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.

Thanks Enigma. Spent most of the PM investigating INSTEAD OF but doesn't seem to do what I require either.

Can you actually do a Delete from where statement within a trigger 'cos I haven't found one in any of the examples I've searched today.|||Forget reading up on instead of triggers...there's just so much wrong here...

You do not want to do nolock...

and I don't think (damn that happend a lot) that a trigger will fire for any uncomitted data anyway...

The entire avenue your heading down shows that your trying to mess with things that you shouldn't

What are you trying to do...in non technical terms?

For example you mention you want to prevent dups...

ok, simple, put a contraint on the columns you would consider to be be dups...

what else?|||Originally posted by Brett Kaiser

What are you trying to do...in non technical terms?

For example you mention you want to prevent dups...


Brett
The original trigger populates a table that is used to automate emails from our call logging system (with VB). If a call is assigned a certain action (completed or pending user) then the trigger fires. Several mails are sent and if there is no movement on the call after a certain period the call is automatically closed using the VB app.

The problem I have is that the same call could be released but a few days later given the same action again and unless I can delete the original row the call will be closed on the original closure date.

Hope this makes it a bit clearer what i am trying to acheive

DELETE and UPDATE Trigger question

Hello
I have a Trigger on a table. Here is the code

ALTER TRIGGER [dbo].[OnOrderDelete]ON [dbo].[orders] AFTERDELETE,UPDATEAS BEGINSET NOCOUNT ON;DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;END

Actually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:
When orders table is updated, the

"SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;"

part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @.ids =(SELECT idfromUPDATED) ")

Is this right? And how can I part UPDATED and DELETED ?

Thanks
Artashes

Because an update is logically a delete and insert combined into one atomic transaction.

And just an FYI - your trigger will fail if you ever try to delete multiple rows with a single statement. You should try:

DELETE FROM files where OrderID IN (SELECT ID FROM deleted)

|||

Thanks for information!

Your way of deleting seems to be more correct!

But you know, my query works!!

Is there any explanation?

|||

Motley has given the explanation. In SQL Server there is no actually 'UPDATE' as you may expect just some modification on a row, instead an UPDATE consists an INSERT followed by a DELETE, so that's why you felt that DELETED is treated as UPDATED.

|||

Iori_Jay, about DELETE and INSERT everything is clear.

I mean why works my query

DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;

Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write

DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);

|||

Try executing DELETE FROM files.

With your trigger, it will fail.

|||

Motley, I don't understand you!

And what am I executing now?

Or you mean only just "DELETE FROM files;" (delete all rows in files?)

|||Yes, if you used the trigger you originally had and tried to delete all the rows in files, the trigger would throw an error. Any delete that caused multiple rows to be deleted would fail.|||

I don't know about all files, but there were situations, when my query deleted 3 rows!

|||

Not within a single statement.

DELETE from files where fileid=1

DELETE from files where fileid=2

would work, but...

DELETE from files where fileid=1 or fileid=2

would fail.

|||

Thanks for info!

But there is no statement like "DELETE from files where fileid=1 or fileid=2" in my query? Am I right?

|||

artashes:

Iori_Jay, about DELETE and INSERT everything is clear.

I mean why works my query

DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;

Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write

DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);

I can't understand why such query works even when you're trying to delete multiple rows--the subquery will return multiple rows and "SELECT @.ids =(SELECT idfrom DELETED)" command will fail as it is trying to assign multiple values to a single variable.However the following query will work as it assigns the last id from deleted table to the variable:

DECLARE @.idsint;
SELECT @.ids =idfrom DELETED;
DELETE FROM filesWHERE OrderId = @.ids;

||| If you wan't I can send you a project where it works, and deletes NOT only the last deleted id.

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

Defining Variables in Date fields within a Trigger?

Hi All,

I am creating an Insert Trigger with following example of code for you to go off(just an example)

DECLARE @.CREATIONDATE VARCHAR(12)
SET @.CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
Values ('Name', 'Code', 'Description', {d @.CREATIONDATE})

Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

When checking the Syntax for the trigger it errors saying that - 'Error Syntax near '@.CREATIONDATE'

It works fine if I just insert a static value such as
{d '2002-10-10'}. Am I able to replace the static value with a variable and if so what will my syntax be? How would it look?

Thanks
Anthonyhow about:

Insert into fintest.dbo.glf_chart_acct
(chart_name, accnbri, descr1, date)
select 'Name', 'Code', 'Description', Creation_Date
from Inserted

SQL Server will automatically convert a string to a date and you have the advantage of handeling one or more records at a time!|||DECLARE @.CREATIONDATE VARCHAR(12)
SET @.CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
Values ('Name', 'Code', 'Description', {d @.CREATIONDATE})

Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

How about instead of the {d @.CREATIONDATE} you either put just @.CREATIONDATE or try a CAST(@.CREATIONDATE as datetime)