Help creating a trigger please

[ideasworking]

Hello,

I have never created a trigger before so... I'd like some help creating a trigger. I would like the trigger to do the following. When a new record is inserted into TABLE1 I would like to evaluate the inserted TABLE1.VALUE1 and if the inserted VALUE1 < 1 and UNITLOG.dbo.UNIT1.STATE = 'A' then insert a new record in a different database.

The purpose of this trigger is to log state changes. Later I will create other similiar triggers with different threshold values.

So far this is what I have... any help would be greatly appreciated.

-- #################################################
CREATE TRIGGER [UNITTRIP] ON [dbo].[TABLE1]
FOR INSERT
AS

DECLARE @LOAD float, @PREVSTATE nchar(2)
SET @PREVSTATE = (SELECT STATE FROM UNITLOG.dbo.TABLE1 ORDER BY DateAndTime DESC)

SET @LOAD = (SELECT VALUE1 FROM INSERTED)

IF @LOAD < 1 AND @PREVSTATE = 'A'
INSERT INTO UNITLOG.dbo.UNIT1 (DateAndTime, State) VALUES (GetDate(),'D')

 

[jbenson001]

SET @PREVSTATE = (SELECT STATE FROM UNITLOG.dbo.TABLE1 ORDER BY DateAndTime DESC)

This will cause more than 1 row returned if there is more than one row in the table. You need to specify a where clause or TOP 1 so that there is only 1 row returned, otherwise you will get an error.