trigger to insert row in another table

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

SQL Server 2005 Forums

Transact-SQL (2005)

trigger to insert row in another table

Author

Topic

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-06-13 : 02:04:15

hi all

I have a table in sql in which every time new data is inserted some time i update this table.i just want to copy the new inserted and updated record into another table.How can i do this. .please help me. this trigger is working fine when inserting data into one table ond copy into another table but when i update first table and insert row in another table with only modified column into first table

Alter TRIGGER [dbo].[UpdateBalance]
ON [dbo].[Items]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @RecordID BIGINT

DECLARE @Debit VARCHAR(50)
DECLARE @Credit VARCHAR(50)
DECLARE @Status VARCHAR(50)

SET @RecordID=(SELECT Id FROM INSERTED)

SET @Debit=convert(varchar,(SELECT Debit FROM INSERTED))
SET @Credit=(SELECT Credit FROM INSERTED)
SET @Status=(SELECT [Status] FROM INSERTED)

case when @Status='1' then
insert into Inhand(Onhand) values(@Credit)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-13 : 02:50:47

you need to assume there might be more than one rows in the INSERTED or DELETED table.

you should write it something like


insert into <sometable> ( <column list> )
select <column list>
from   inserted

KH
[spoiler]Time is always against us[/spoiler]

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-06-13 : 03:19:59

hi
thanks to reply me
but i have to insert a copy of updated row (which is updated in first table) but before insertion in second table we have to chack a condition with status
just like that
if status=1 then copy of upated row should be inserted in secoond table other that should not be insert
please help me reply me as soon as possible its urgent
thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-13 : 03:24:49

[code]
insert into <sometable> ( <column list> )
select <column list>
from inserted
WHERE STATUS = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources