Problems With Trigger 1

[chuckh70]

Hello,

I am in need of pulling a couple of fields from a table on insert and put them into another table so that I can do searches and inserts without touching the main table. I created the trigger below for this purpose, but for some reason it stops the insert all together. If I set this to Update it works fine.

I think mwhat might be happening is the app isn't done inserting info when the trigger fires off. Would is be better to make this a stored procedure? how would I do that, and how would I make that fire off each time a record is inserted?

CREATE TRIGGER [trgMachine_Status] ON [dbo].[machine] 
FOR INSERT 
AS

DECLARE @machineID INT
DECLARE @machineName VARCHAR(64)
DECLARE @Serial_Number VARCHAR(32)

SELECT @machineID = (SELECT id FROM Inserted)
SELECT @machineName = (SELECT Name  FROM Inserted)
SELECT @Serial_Number = (SELECT serial_number FROM Inserted)

INSERT INTO machine_status (sid, name, serial_number) values (@machineID,@machineName,@Serial_Number)

 

[SQLSister]

Ok your first problem is that this trigger will never work if multiple records are inserted. Triggers should always be designed to handle multiple records in the inserted or deleted pseudotables.

CREATE TRIGGER [trgMachine_Status] ON [dbo].[machine] 
FOR INSERT 
AS
INSERT INTO machine_status (sid, name, serial_number) 
Select id, name, serialnumber From inserted

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[chuckh70]

That was the problem Exactly.. Thanks