Insert Triggers - 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 2000 Forums

SQL Server Development (2000)

Insert Triggers

Author

Topic

PieroMaggio
Starting Member

5 Posts

Posted - 2001-12-19 : 06:38:21

I'm new to triggers but if I explain what I want to do then hopefully someone can help:
I have a table and into that table I insert data. There is a time col and date col, of which I wish to join these 2 seperate pieces of info and then store the joined Time&date in another col called TSTAMP. Time col & Date col are CHAR and TSTAMP is DATETIME.

I have wrote a trigger that does this on insert:

CREATE TRIGGER [CreateU1TStamp] ON [UNIT1]
FOR INSERT
AS
update unit1 set tstamp = convert(datetime, (ALM_DATELAST + ' ' +
ALM_TIMELAST), 3) where tstamp is null

The problem is that I guess that this will search every entry in the table instead of the entry that the trigger has fired on.

This is putting a massive overhead on my system and making the system very slow.

I`m sure what I have done is wrong, so could someone please put me straight.

CHEERS

Piero

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2001-12-19 : 07:05:29

Your update should look something like this (not tested):

update unit1
set tstamp = convert(datetime, (ALM_DATELAST + ' ' +
ALM_TIMELAST), 3)

where <PrimaryKeyColumnName> IN
(
SELECT <PrimaryKeyColumnName>
FROM inserted
)

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-19 : 07:39:29

CREATE TRIGGER [CreateU1TStamp] ON [UNIT1]
FOR INSERT
AS
update unit1
set tstamp = convert(datetime, (unit1.ALM_DATELAST + ' ' + unit1.ALM_TIMELAST), 3)
from inserted
where unit1.<PrimaryKeyColumns> = inserted.<PrimaryKeyColumns>

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

PieroMaggio
Starting Member

5 Posts

Posted - 2001-12-19 : 09:52:30

NR
Could you explain the PrimaryKeyColumns bit
Cheers

quote:

CREATE TRIGGER [CreateU1TStamp] ON [UNIT1]
FOR INSERT
AS
update unit1
set tstamp = convert(datetime, (unit1.ALM_DATELAST + ' ' + unit1.ALM_TIMELAST), 3)
from inserted
where unit1.<PrimaryKeyColumns> = inserted.<PrimaryKeyColumns>

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Nazim
A custom title

1408 Posts

Posted - 2001-12-19 : 10:01:57

here what NR means by primary key is the keys which are common between yourtables (Here the one on which trigger is written and the which you are trying to update) there might be some relation between these two tables , by joining this way the update finds only the matching records and updates it .

HTH

-------------------------
"Success is when Preparedness meets Opportunity"

Edited by - Nazim on 12/19/2001 10:03:30

PieroMaggio
Starting Member

5 Posts

Posted - 2001-12-19 : 10:26:58

It all occurs on the same table.

quote:

here what NR means by primary key is the keys which are common between yourtables (Here the one on which trigger is written and the which you are trying to update) there might be some relation between these two tables , by joining this way the update finds only the matching records and updates it .

HTH

-------------------------
"Success is when Preparedness meets Opportunity"

Edited by - Nazim on 12/19/2001 10:03:30

Nazim
A custom title

1408 Posts

Posted - 2001-12-19 : 10:40:14

ok, then ur trigger will be

update i
set i.tstamp = convert(datetime, (unit1.ALM_DATELAST + ' ' + unit1.ALM_TIMELAST), 3)
from inserted i

-------------------------
"Success is when Preparedness meets Opportunity"

PieroMaggio
Starting Member

5 Posts

Posted - 2001-12-19 : 11:04:15

Please could you show the whole trigger as this is coming up with an error.

quote:

ok, then ur trigger will be

update i
set i.tstamp = convert(datetime, (unit1.ALM_DATELAST + ' ' + unit1.ALM_TIMELAST), 3)
from inserted i

-------------------------
"Success is when Preparedness meets Opportunity"

Nazim
A custom title

1408 Posts

Posted - 2001-12-19 : 11:12:50

oops! am sorry
u cant update on logical tables that leaves u with nr's statement. c ,u need to create a primarky key in your table if there isnt any.
a auto identity field should do ,if u dont have anything specific field as primary key or can be changed into a primarkey key. and use nr's statement that should do the trick

-------------------------
"Success is when Preparedness meets Opportunity"

Edited by - Nazim on 12/19/2001 11:14:03

PieroMaggio
Starting Member

5 Posts

Posted - 2001-12-19 : 11:16:28

Cheers
That should sort it.

quote:

oops! am sorry
u cant update on logical tables that leaves u with nr's statement. c ,u need to create a primarky key in your table if there isnt any.
a auto identity field should do ,if u dont have anything specific field as primary key or can be changed into a primarkey key. and use nr's statement that should do the trick

-------------------------
"Success is when Preparedness meets Opportunity"

Edited by - Nazim on 12/19/2001 11:14:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources