Trigger Question

[Chico Che]

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.

[Russell Fields]

Re: Trigger Question

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" <jsisk24@yahoo. comwrote in message
news:Xns9A446B6 662F69Chicoche@ 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.

[Chico Che]

Re: Trigger Question

First, thanks for the response

But keep getting invalid object name on the table

[Russell Fields]

Re: Trigger Question

Please show your code. And which table has the invalid name? Etc?

RLF

"Chico Che" <jsisk24@yahoo. comwrote in message
news:Xns9A448ED 62D4EAChicoche@ 216.196.97.131. ..

First, thanks for the response
>
But keep getting invalid object name on the table

[Chico Che]

Re: Trigger Question

"Russell Fields" <russellfields@ nomail.comwrote in news:##pzw30bIH A.5348
@TK2MSFTNGP03.p hx.gbl:

Please show your code. And which table has the invalid name? Etc?
>
RLF
>
"Chico Che" <jsisk24@yahoo. comwrote in message
news:Xns9A448ED 62D4EAChicoche@ 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

[Erland Sommarskog]

Re: Trigger Question

Chico Che (jsisk24@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Russell Fields]

Re: Trigger Question

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" <jsisk24@yahoo. comwrote in message
news:Xns9A44953 9969EEChicoche@ 216.196.97.131. ..

"Russell Fields" <russellfields@ nomail.comwrote in news:##pzw30bIH A.5348
@TK2MSFTNGP03.p hx.gbl:
>

>Please show your code. And which table has the invalid name? Etc?
>>
>RLF
>>
>"Chico Che" <jsisk24@yahoo. comwrote in message
>news:Xns9A448E D62D4EAChicoche @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
>

[Chico Che]

Re: Trigger Question

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

[Russell Fields]

Re: Trigger Question

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" <jsisk24@yahoo. comwrote in message
news:Xns9A4587B 49E02EChicoche@ 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

[Hugo Kornelis]

Re: Trigger Question

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