By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,002 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,002 IT Pros & Developers. It's quick & easy.

Trigger Question

P: n/a
Have a table that has following fields (pkid, field1, field2, field3,
field4). I need to create a trigger that will insert a row into another
table with the pkid column that was updated. Any help will be appreciated.
Feb 14 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Chico Che,

This is just an outline since I don't have your table structures, but the
pseudo-table 'inserted' contains the data of any inserted rows and the after
data for any updated rows.

INSERT INTO AnotherTable
SELECT pkid FROM inserted

RLF

"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.109.144...
Have a table that has following fields (pkid, field1, field2, field3,
field4). I need to create a trigger that will insert a row into another
table with the pkid column that was updated. Any help will be appreciated.

Feb 14 '08 #2

P: n/a
First, thanks for the response

But keep getting invalid object name on the table
Feb 14 '08 #3

P: n/a
Please show your code. And which table has the invalid name? Etc?

RLF

"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
First, thanks for the response

But keep getting invalid object name on the table

Feb 14 '08 #4

P: n/a
"Russell Fields" <ru***********@nomail.comwrote in news:##pzw30bIHA.5348
@TK2MSFTNGP03.phx.gbl:
Please show your code. And which table has the invalid name? Etc?

RLF

"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
>First, thanks for the response

But keep getting invalid object name on the table


CREATE TRIGGER iolta_change on tbldata
AFTER INSERT AS
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END

Feb 14 '08 #5

P: n/a
Chico Che (js*****@yahoo.com) writes:
>>First, thanks for the response

But keep getting invalid object name on the table

CREATE TRIGGER iolta_change on tbldata
AFTER INSERT AS
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END
I will need to ask: what do you really expect us to help with? What
the name is of your tables?

The only thing I can say is that the above looks funny. Does tblaudit
only have a single column? Best practice is to always include a column
list with the INSERT statement to state which columns you are inserting
into.

Also, in your original post, you talke about updates. Here you have a
trigger for INSERT.

I'm afraid that if you don't give us more detail on what you want to
achieve, that we will not be able to help you.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 14 '08 #6

P: n/a
Chico,

Perhaps this link will help you understand what we are looking for. Read
the article "Please provide DDL and sample data" at
http://www.aspfaq.com/etiquette.asp?id=5006

RLF

"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
"Russell Fields" <ru***********@nomail.comwrote in news:##pzw30bIHA.5348
@TK2MSFTNGP03.phx.gbl:
>Please show your code. And which table has the invalid name? Etc?

RLF

"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
>>First, thanks for the response

But keep getting invalid object name on the table



CREATE TRIGGER iolta_change on tbldata
AFTER INSERT AS
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END

Feb 15 '08 #7

P: n/a
We have two tables

Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55)
field4 varchar(55))

Create Table tblAudit
(recid int not null)

A program is contantly updating or insert rows in table tblData. We want to
create a trigger that will insert the pkid from tblData into tblAudit when
a new row is inserted or a row is updated.

I apologize, first time poster.

Thanks
Feb 15 '08 #8

P: n/a
Chico Che,

Aside from a missing comma in your tblData DDL, everything works just fine.
I created the tables, the trigger from the previous post, inserted data and
the trigger inserted data to the audit table. So, I still don't know what
error you are getting, unless it was that tblData was not created. But your
trigger was also only for INSERT not for UPDATE.

So...

Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55), -- Added a comma here
field4 varchar(55))

Create Table tblAudit
(recid int not null)

CREATE TRIGGER iolta_change on tbldata
AFTER INSERT, UPDATE AS -- Added for both update and insert
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END

Having said that, Erland correctly pointed out the tiny audit table is not
very useful for auditing. Perhaps at least tracking who made the change and
when:

Create Table tblAudit
(recid int not null,
loginname nvarchar(128),
changedatetime datetime)

CREATE TRIGGER iolta_change on tbldata
AFTER INSERT, UPDATE AS
BEGIN
INSERT INTO tblaudit
SELECT pkid,
SUSER_SNAME(), -- current login name
GETDATE() -- current date and time
FROM inserted
END

Then if you want a delete trigger, you can create one of those as well.

RLF
"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
We have two tables

Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55)
field4 varchar(55))

Create Table tblAudit
(recid int not null)

A program is contantly updating or insert rows in table tblData. We want
to
create a trigger that will insert the pkid from tblData into tblAudit when
a new row is inserted or a row is updated.

I apologize, first time poster.

Thanks

Feb 15 '08 #9

P: n/a
On Fri, 15 Feb 2008 14:20:57 -0600, Chico Che wrote:
>We have two tables

Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55)
field4 varchar(55))

Create Table tblAudit
(recid int not null)

A program is contantly updating or insert rows in table tblData. We want to
create a trigger that will insert the pkid from tblData into tblAudit when
a new row is inserted or a row is updated.

I apologize, first time poster.

Thanks
Hi Chico,

The table is called tblAudit (capital A) here, but tblaudit (small a) in
the trigger code you posted. If your database uses a case sensitive
collation, these names are considered different.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Feb 15 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.