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