Setting column value in a trigger.

[williamu]

Hi,

How do I set a column's value within a trigger? For example I have a table with 2 fields A. & B. B's data is user supplied and A's is inserted in a trigger, how do I do it?

I can't find the answer after 30 mins of msdn and googling.

Can anyone tell me please?.

 

[hmckillop]

try something like this

CREATE TRIGGER inTable1
ON TABLE1
FOR INSERT AS
      INSERT INTO table2
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT  
            ins.emp_id,
            ins.emp_bankAccountNumber,
            ins.emp_salary,
            ins.emp_SSN
         FROM inserted ins

GO

The inserted table contains the entries of the newly inserted rows.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[williamu]

This may work but I want to set the column value for the table the trigger is executing on. This INSERTs into another table. What I want to do is UPDATE Inserted but as this can't be done using UPDATE Inserted SET xxx = yyy, how do I do it?

 

[nomadicalloy]

please look at following

http://www.tek-tips.com/viewthread.cfm?qid=1084586

SQL Help Blog

http://www.xpherion.com/blog.asp

 

[hmckillop]

Can you give a data example. As I read through the original post and not clear after second post.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[williamu]

Nope it still doesn't work, either that or I'm just missing the point. I want to create an ID that it raised to the power of 2 on each insert, and I want to use a trigger for this.

Here's what I've got.

CREATE TRIGGER Attribs_Sequence_ID ON Attribs FOR INSERT AS
DECLARE @CurrID INT
DECLARE @NewID INT
SET @CurrID = (SELECT MAX(Attribs_ID) FROM Attribs)
SET @NewID = 0
BEGIN
	IF (@CurrID = 0)
		SET @NewID = 1
	ELSE IF (@CurrID = 1)
		SET @NewID = 2
	ELSE IF (@CurrID >= 2)
		SET @NewID = POWER(@CurrID, 2)
	IF (@NewID >= POWER(31, 2))
		RAISERROR('You have reached your limit of 30 Attributes', 16, 1)
	BEGIN
		UPDATE Attribs SET Attribs_ID = @NewID
		WHERE (Legend = Inserted.Legend)
	END
END
GO

Whenever I try to do an INSERT I get:

Cannot insert the value NULL into column 'Attribs_ID', table 'dbo.RoomAttribs'; column does not allow nulls. INSERT fails.
The statement has been terminated.

 

[amrita418]

Looks like you have problems with the IF-Else conditions.

Replace your code with this and let us know your findings:

    	IF (@CurrID = 0)
            SET @NewID = 1
	IF (@CurrID = 1)
            SET @NewID = 2
    	ELSE 
	    SET @NewID = POWER(@CurrID, 2)

Regards,
AA

 

[williamu]

Thanks to all who helped, I worked this one out yesterday and I'm please to say it now does as expected.

If anyone wishes to see the finished trigger then please ask.