Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
USE [RF_WORLD]
GO
/****** Object:  Trigger [dbo].[lastdeathtrigg]    Script Date: 08/20/2012 10:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Mauro Tamm
-- =============================================
ALTER TRIGGER [dbo].[lastdeathtrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS 
IF (UPDATE(Death))
BEGIN
    SET NOCOUNT ON;
DECLARE @userserial int;
SELECT @userserial ='serial' WHERE UPDATE(Death)
Insert INTO [RF_WORLD].[dbo].[lastdeath]
(serial)
VALUES (@userserial)
END

The where part is suspicious, woudl it work like that? essentially i need it to only select the row serial value if it actually updated.

share|improve this question
    
There are... many... suspicious parts. Can you describe for us the end result you want? It's really unclear what's going on from this code snippet. –  Jon Seigel Aug 20 '12 at 16:22
add comment

1 Answer

up vote 6 down vote accepted

Several problems with your trigger:

  1. You are assuming the trigger fires per row, whereas triggers fire per statement. This means you can't just assign a variable because this won't work if the update affects more than one row.
  2. Your query to assign a variable doesn't pull from anywhere - you need to reference the inserted and/or deleted pseudo-tables.
  3. Checking UPDATE() only tells you that the column was referenced, but it doesn't tell you whether the value has changed.

Here is how I would re-write your trigger:

-- this assumes Death is not nullable

ALTER TRIGGER [dbo].[lastdeathtrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS 
IF (UPDATE(Death))
BEGIN
  SET NOCOUNT ON;

  INSERT RF_WORLD.dbo.lastdeath(serial)
    SELECT i.serial FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.serial = d.serial 
    AND i.Death <> d.Death;
END
GO
share|improve this answer
    
yeah i see now, looks like i simply wasn't aware of this option/inner join feature. Select The i.serial is a little unclear. If its just getting defined AS i, how could it select it before it Was defined? –  Agony Aug 20 '12 at 18:31
    
FROM is parsed before SELECT, WHERE etc. You can reference the alias constructed in FROM in other clauses. –  Aaron Bertrand Aug 20 '12 at 18:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.