Create trigger add rows on insert and update

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)

Create trigger add rows on insert and update

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-28 : 11:45:24

I am using this trigger, on update and on insert. but it is creating almost 30 to 40 rows in one shot.

When i modify a record, just that record should be cerated in log table as a backup info same should happen when i insert a new record to table.

this trgigger should create that newly inserted record to log table.

is there anything wrong with my trigger.

identity column is activityid hich has unique value.

CREATE TRIGGER [dbo].[insTab_ccsNetWorkflowActivity_log] ON [dbo].[Tab_ccsNetWorkflowActivity] 
FOR INSERT, UPDATE
AS

DECLARE @TimeStamp datetime

Set @TimeStamp = GetDate()

INSERT INTO Tab_ccsNetWorkflowActivity_log
([ActivityID],
	[ModuleRecordID],
	[ModuleName],
	[Step],
	[Type],
	[AssignedTo],
	[Description],
	[DueDate],
	[DoneDate],
	[Disposition],
	[Comments],
	[Critical],
	[EmailFlag],
	[Date_Updated],
	[IsRejected],
	[IsPlaceHolder])
Select  
ins.[ActivityID],
	na.[ModuleRecordID],
	na.[ModuleName],
	na.[Step],
	na.[Type],
	na.[AssignedTo],
	na.[Description],
	na.[DueDate],
	na.[DoneDate],
	na.[Disposition],
	na.[Comments],
	na.[Critical],
	na.[EmailFlag],
	na.[Date_Updated],
	na.[IsRejected],
	na.[IsPlaceHolder]
FROM inserted ins JOIN Tab_ccsNetWorkflowActivity na ON 
na.ActivityID = ins.ActivityID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:51:00

for update it will capture new values also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-28 : 11:59:46

Hello Visakh,

Am i doing anything wrong with the trigger.

even after when i inserted just one record it put like 58 rows in the log table.

where did those 58 rows came, i have no clue.

Thank you.

Kristen
Test

22859 Posts

Posted - 2010-04-28 : 12:06:49

na.ActivityID = ins.ActivityIDis not unique?

Kristen
Test

22859 Posts

Posted - 2010-04-28 : 12:07:34

Why join to Tab_ccsNetWorkflowActivity at all? why not just use the row(s) in INSERTED ?

Kristen
Test

22859 Posts

Posted - 2010-04-28 : 12:08:28

P.S. Also, you aren't using @TimeStamp (which I presume was your intention?)