Update Trigger Help - SQL Server Forums

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)

Update Trigger Help

Author

Topic

kallileo
Starting Member

21 Posts

Posted - 2007-04-24 : 04:55:03

I have a table in my db with only one record and I update one cell from my ASP.NET page. What I need is every time I update the cell the whole record to inserted in a second table.

Create trigger Update_table2 on table1

For update

as
insert into table2(table2_cell1, table2_cell2, table2_cell3, table2_cell4).

Is this code OK????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 04:57:27

[code]
insert into table2 (table2_cell1, table2_cell2, table2_cell3, table2_cell4)
select cell1, cell2, cell3, cell4
from inserted
[/code]

assuming cell1, cell2 etc are columns of table1

kallileo
Starting Member

21 Posts

Posted - 2007-04-24 : 05:17:27

Thanks.
Another question is where do I create the trigger in SQL Server Express or Visual Web Developer????
I know how and where to create Stored Procedures but I can find a way to create triggers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 05:30:41

Just open up a query window to the SQL Server and run your create trigger script there.

kallileo
Starting Member

21 Posts

Posted - 2007-04-24 : 06:43:25

Is this OK now?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>

CREATE TRIGGER update_table2
ON table1
FOR UPDATE
AS
INSERT INTO table2(cell1,cell2,cell3,cell4)
SELECT cell1,cell2,cell3,cell4
FROM table1

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here

END
GO

Do I need the SET ANSI_NULLS ON, BEGIN,SET NOCOUNT ON statements?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 06:48:25

[code]
CREATE TRIGGER update_table2
ON table1
FOR UPDATE
AS
INSERT INTO table2(cell1,cell2,cell3,cell4)
SELECT cell1,cell2,cell3,cell4
FROM ~~table1~~ inserted
[/code]

kallileo
Starting Member

21 Posts

Posted - 2007-04-24 : 07:14:31

What does "inserted" mean?

Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 07:17:59

inserted is a special table that only existed in trigger. It contains all rows of a table when record was inserted into a table. It also contains record changed that were affected by the UPDATE operation.

refer to BOL for more information

quote:

Using the inserted and deleted Tables
Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.

The inserted and deleted tables are used primarily in triggers to:

Extend referential integrity between tables.

Insert or update data in base tables underlying a view.

Check for errors and take action based on the error.

Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

kallileo
Starting Member

21 Posts

Posted - 2007-04-25 : 03:43:29

Thanks again.

Also I want insert into cell4 of the table2 the date and time of the update.

CREATE TRIGGER update_table2
ON table1
FOR UPDATE
AS
INSERT INTO table2(cell1,cell2,cell3,cell4)
SELECT cell1,cell2,cell3, getdate()
FROM inserted

?????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 03:46:31

quote:
Originally posted by kallileo

Thanks again.

Also I want insert into cell4 of the table2 the date and time of the update.

CREATE TRIGGER update_table2
ON table1
FOR UPDATE
AS
INSERT INTO table2(cell1,cell2,cell3,cell4)
SELECT cell1,cell2,cell3, getdate()
FROM inserted

?????

Yes. correct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources