help with a trigger

[nathanharcup]

Hi All,
I have created the below trigger but cannot figure out why the delete statment does not work? all other statments work as expected.
If i change the variable to the actual value it does work..

Thanks

alter TRIGGER [tr_sitelocation] ON dbo.usr_refsitelocation
FOR INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @usr_sitelocation varchar(255),
@usr_guid uniqueidentifier,
@usr_deleted int
-- Insert statements for trigger here
Select @usr_sitelocation = usr_sitelocation,
@usr_guid = usr_guid,
@usr_deleted = usr_deleted
From Inserted
--Delete from the departments table if the entry has been deleted
If @usr_deleted = 1
begin
update set [Location] = NULL where EU_Location = @usr_sitelocation

*************does not run ***************
delete from [Location] where [Location] = @usr_sitelocation
*************does not run ***************
end

if not exists (select Location from [Location] where Location = @usr_sitelocation)
begin
insert into [Location values] (@usr_sitelocation)
end
END
GO

 

[RiverGuy]

For one, your trigger will not work in cases where more than one row is inserted or updated at a time. The entire body could be rewritten to something like the following (untested):

UPDATE a 
SET [Location] = NULL
FROM Whatever a
INNER JOIN INSERTED b
ON a.EU_Location = b.usr_sitelocation

DELETE a
FROM [Location] a
INNER JOIN INSERTED b
ON a.[Location] = b.usr_sitelocation


INSERT INTO [Location Values]
SELECT a.usr_sitelocation
FROM INSERTED a
LEFT OUTER JOIN [Location Values] b
ON a.usr_sitelocation = b.[Location]
WHERE b.[Location] IS NULL

 

[nathanharcup]

Thanks for the response, the application im using will only alow one update at a time, however I have changed to the above but the delete statment still does not run, the other statments do run OK

even the statment inside the same if statment.

I wonder if the inserted value is wrong somehow but cant see how (i dont even see it trying the statement inside sql profiler)

Thanks

 

[RiverGuy]

You can't rely on there being only one update at a time. This application might run for years after you've moved on to another job, and someone might get the bright idea to import data from another system, or do a mass insert/update because of some business change requirement.

As for why it's not deleting, I'm not sure. You can try a test by changing the DELETE to a SELECT, just to see if anything is returned. Then, run a test in Management Studio against a test version of the tables to see if anything is returned

Instead of

DELETE a
FROM [Location] a
INNER JOIN INSERTED b
ON a.[Location] = b.usr_sitelocation

try

SELECT b.*, a.*
FROM INSERTED b
LEFT OUTER JOIN
[Location] a
ON b.usr_sitelocation = a.[Location]

for your test

 

[markros]

Is it possible that Location table has its own delete trigger preventing deletion?

 

[nathanharcup]

Hi All the select statment resturs the row i want deleted

there are no triggers on the location table

 

[RiverGuy]

You're sure it returns the row you want deleted? If the "a" columns are all null, then it's not finding a match for what is in the INSERTED table. If it really is returning the row you want deleted, I can't think of a reason why the row would not be deleted unless you had a trigger on the table as markros suggested.

 

[nathanharcup]

yes, definatly returns the value i wasnt to delete when i run the select statment, there are no null values in either table, there if definatly no triggers other than this one that im creating...
it's finding the match because its running the update statement using the same value im using to match on without issue.

 

[danvlas]

your trigger is defined FOR INSERT, UPDATE.

Add 'DELETE' and it will work.

HTH


Daniel Vlas
Systems Consultant

 

[nathanharcup]

found that the index was causing the delete to fail??

if I remove the non-unique / non-clustered index the delete statement works fine

If present it does not delete, even after a re-build of the index, re-org of the index ???

Nathan

 

[nathanharcup]

OK rowlocks and pagelocks caused the issues, I unchecked these values on the index and all now works as expected