Triggers with parameters.

Question

Triggers with parameters.

Snigdha Sinha

SSC-Addicted

Points: 470
More actions
December 5, 2005 at 1:03 pm

#90161

Hi,
I have a situation where I think a trigger should work but I am not able to make it work. I have a table where I am stroing the details of a Test Request. Now, whenever a column named TestRequestStateId changes, I want to enter some of the details from this table into a history table.
Now the problem is if I write a trigger, to track down the column update, I cannot pass parameters to it. And I need to have the TestRequestNo (Primary key of the table) to get that record whose data I have to store in the History table. So how do I write a trigger which will insert a record in History table based on the TestRequestNo.
If this is not possible with a trigger, what other solution is possible. Please let me know if anyone comes up with an idea for this one.
Thanks,
Snigdha

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

You will have to check the "Deleted" table for the values that you want to insert into your history table...based on the TestRequestNo...

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

something like this (if you're updating only one row at a time)...

CREATE TRIGGER trgInsertIntoHistory ON [dbo].[tblTestRequest] 
FOR UPDATE
AS

BEGIN
INSERT INTO tblHistory
SELECT col1, col2 FROM Deleted
END

**ASCII stupid question, get a stupid ANSI !!!**

Vinoo Say Hey Kid Points: 670 More actions · Answer 3

I think that there is one more way to do this- Update the table , then check for the @@rowcount. If @@rowcount > 1 then perform the insert operation -

1. Update stmt

2. if @@rowcount > 1 then

Insert statement

Snigdha Sinha SSC-Addicted Points: 470 More actions · Answer 4

Hey,

Thanks a lot...

This is exactly wht I was looking for. Now I am right on target.

Snigdha

David Korb SSCrazy Points: 2505 More actions · Answer 5

If you want to get more functionality in your programming, take a look at books-online first. For example: why miss out on something like "IF UPDATE"...

IF UPDATE (column)

BEGIN

INSERT INTO audit_table(column)

SELECT t.column

FROM deleted as d

INNER JOIN base_table as t

ON d.id = t.id

END