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 certain rows

I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
table? I'm familiar with the DELETE command but as far as I know it can
only delete one row at a time. How does one delete multiple rows?
Thanks.
RussI found my answer, you can delete multiple rows with the DELETE command.
Russ
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:TWPXi.1645$It.97@.trndny06...
> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
> table? I'm familiar with the DELETE command but as far as I know it can
> only delete one row at a time. How does one delete multiple rows?
> Thanks.
> Russ
>|||Yes, as you found you can delete rows between 150 and 210 using DELETE.
For example = DELETE FROM Users where [value] >= 150 and [value] <= 210
--
Ekrem Önsoy
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:DGTXi.1664$It.1389@.trndny06...
>I found my answer, you can delete multiple rows with the DELETE command.
> Russ
>
> "Russ Hromyko" <rhromyko@.verizon.net> wrote in message
> news:TWPXi.1645$It.97@.trndny06...
>> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
>> table? I'm familiar with the DELETE command but as far as I know it can
>> only delete one row at a time. How does one delete multiple rows?
>> Thanks.
>> Russ
>

Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>

Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them
.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excludin
g
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:

> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:

> Have you compared the before and after results. Sometimes due to the way y
ou
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
>
>