urgent trigger problem

[fgeorge]

i want to create trigger at the row level to modify a field in each record that is inserted..
i do not have a primary key on my table...
the code that doesnt work is below..


CREATE TRIGGER tr_msisdn_ProcessedRedemption
ON ProcessedRedemption
FOR INSERT
AS
UPDATE ProcessedRedemption
SET msisdn ='0'+ RIGHT((select msisdn from inserted),10)
where (msisdn=(SELECT msisdn FROM inserted))


1) do i need to put the WHERE clause?
2) can someone show me where/how to correct my code?
thanks a million.

 

[ClaireHCM]

Do you want to update all the records in the table that has been inserted?

If this is the case...you dont need a trigger for this.
a single update statement can do it

update ProcessedRedemption set msisdn = '0'+msisdn

Maybe I misunderstand your requirement.
Let me know

 

[fgeorge]

i want the change to be made as soon as the record is inserted..

 

[JamesLean]

Use an INSTEAD OF trigger to do this:

CREATE TRIGGER tr_msisdn_ProcessedRedemption
  ON ProcessedRedemption
  INSTEAD OF INSERT
AS

INSERT ProcessedRedemption (msisdn, col2, col3)
SELECT '0' + RIGHT(msisdn, 10), col2, col3
FROM inserted
go

--James