How to update record being inserted using trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to update record being inserted using trigger

Author

Topic

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-24 : 08:02:07

I am using VB6 and SQL Server 2000. I want to update some fields of the record being inserted/updated using trigger. I tried:-

update CustMst set UpDateDate=getdate() where CustMst.CustID=Inserted.CustID
But it does not work.
Please help me

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 08:07:56

can u explain some what briefly
in ur update statement inserted.custid from which table ur getting that value

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-24 : 08:27:08

Inserted.CustID containing custid being inserted in CustMst Table.I have seen somewhere that 'Inserted' is a table name which holds record being inserted. Anyway, BKLR, I want to replace UpdateDate field with the current date in the only row being inserted/updated. I think now its clear.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 08:32:55

[code]
try this sample procedure

create PROC [dbo].usp_sampproc
(
@ID INT = NULL OUTPUT,
parameters
)
AS
SET NOCOUNT ON
BEGIN

INSERT INTO tablename
(columns.... )
values( @parameters....)

SELECT @id = SCOPE_IDENTITY()

update CustMst
set UpDateDate=getdate() where CustMst.CustID=@id
END
SET NOCOUNT OFF
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 08:46:06

You can use Trigger for it. what have you tried?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 09:46:48

quote:
Originally posted by sha_agrawal

Inserted.CustID containing custid being inserted in CustMst Table.I have seen somewhere that 'Inserted' is a table name which holds record being inserted. Anyway, BKLR, I want to replace UpdateDate field with the current date in the only row being inserted/updated. I think now its clear.

inserted is available only inside the trigger code so if you want to use it you need to write logic inside a insert/update trigger .
just use something like


CREATE TRIGGER yourtriggername
ON CustMst
AFTER INSERT,UPDATE
AS
BEGIN
update c
set c.UpDateDate=getdate() 
fr5om CustMst c
inner join inserted i
on c.CustID=i.CustID
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 09:48:53

or if you're using sql 2005 or higher, you can also use it with OUTPUT operator

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources