Trigger - 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)

Trigger

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 13:03:42

can you tell me what is incorrect in my trigger
CREATE TRIGGER Insert_TRU
ON provcont_new
FOR INSERT
AS

-- only insert the field if the status field is being updated.
IF (SELECT count(1) FROM INSERTED) = 1
begin

update provmstr_new
set pvuser='AGB',
pvpajy=SUBSTRING (dbo.udf_Julian (getdate()) , 3 , 2 ),
pvpajd=right (dbo.udf_Julian (getdate()) , 2 ) ,
pvpadn=cast(datepart(hh,CURRENT_TIMESTAMP) as varchar(2))+ ''+cast(datepart(mi,CURRENT_TIMESTAMP) as varchar(2))+''+cast(datepart(s,CURRENT_TIMESTAMP) as varchar(2))+''+Left(cast(datepart (ms,CURRENT_TIMESTAMP) as varchar(3)),2)

from provmstr_new ge
inner join inserted i on i.pvnum = ge.pvnum
and i.pvuser = ge.pvuser

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
--RAISERROR(65001, 11, 1, "", "")
END
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-01 : 13:15:18

what's the error you get?

Go with the flow & have fun! Else fight the flow

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 13:38:33

I found my problem. However I also notice another issue in this part:
cast(datepart(hh,CURRENT_TIMESTAMP) as varchar(2))+ ''+cast(datepart(mi,CURRENT_TIMESTAMP) as varchar(2))+''+cast(datepart(s,CURRENT_TIMESTAMP) as varchar(2))+''+Left(cast(datepart (ms,CURRENT_TIMESTAMP) as varchar(3)),2)

Because my insert statement is fast I am getting dublicate value. My field size is varchar(8).
Any idea what to do?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-01 : 13:41:59

look into timestamp dataype in BOL.

Go with the flow & have fun! Else fight the flow

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 13:49:23

I am sorry.. But I don't get it... I am using a timestamp...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-01 : 14:21:35

write "timestamp data type" in bol
it's a binary datatype.
it's not a datetime data type.

it is possible that maybe i'm missing your problem entirely...

Go with the flow & have fun! Else fight the flow

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 14:37:09

I got this:
use temp
select @@DBTS
It never changes and also it is more that char(8)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-01 : 14:42:49

quote:
Originally posted by sardinka

I got this:
use temp
select @@DBTS
It never changes and also it is more that char(8)

What are you referring to? I don't see that in your code. If you want us to help, you need to provide more information.

Tara Kizer
aka tduggan

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 14:56:43

My code:
cast(datepart(hh,CURRENT_TIMESTAMP) as varchar(2))+ ''+cast(datepart(mi,CURRENT_TIMESTAMP) as varchar(2))+''+cast(datepart(s,CURRENT_TIMESTAMP) as varchar(2))+''+Left(cast(datepart (ms,CURRENT_TIMESTAMP) as varchar(3)),2)

I am using this part of the code in trigger, however because my insert statement running so fast I am getting dublicate value.
I am looking for unique value from timestamp however my field is only varhcar(8).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-01 : 15:14:01

Just a thought...

Is the "key" (pvnum,pvuser) unique on BOTH tables (provmstr_new and provcont_new)? If not your pvpadn concatenation will produce duplicate rows.

btw,
@@DBTS is not what Sprit1 was referring to. @@DBTS is of timestamp datatype that returns the timestamp of the database. He meant the datatype itself "timestamp". I believe he was suggesting using a column defined as timestamp instead of generating this wacky code.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-01 : 15:14:31

well current_timestamp is same as getdate() which is datetime datatype.
datetime has a 3 ms resolution. therefore anything that fits into that time slot gets the same time.
so you can't differentiate them with datetime.
so you need another column... like identity, guid or timestamp as i mentioned.
example:
CREATE TABLE Table1 (columns, TS_Column timestamp);

EDIT:
basicaly

Go with the flow & have fun! Else fight the flow

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 15:35:37

Can you give me an exmaple how would I use a timestamp.
I still don't understand what are you trying to tell me.
Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-01 : 16:17:11

I believe the point of the timestamp idea is this:

You are currently using a varchar(8) column called 'pvpadn' to hold unique values like '1674210'.
Instead of a varchar(8) column that is a character representation of a point in time, make it a timestamp column. You won't need to generate the value yourself and they will be unique. You can treat that column like you would an identity column in that you don't need to explicitly generate and insert the timestamp values. In fact, an identity column would provide the same functionality.

Spirit1 provided sample code to create a table (just replace his <columns,> with an actual column definition)

Now as a seperate question. Do either of these 2 statement return any rows? If so, that is why you are getting duplicate values from your update statement. If not then....nevermind :)


select pvnum, pvuser, count(*) from provmstr_new group by pvnum, pvuser having count(*) > 1
select pvnum, pvuser, count(*) from provcont_new group by pvnum, pvuser having count(*) > 1

Be One with the Optimizer
TG

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-01 : 16:43:18

I can't change the column datatype. I have to work with it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-01 : 16:54:16

You will not get unique values with current_timestamp. So you'll need to make the changes as described already or update your rows one at a time with a small delay between the updates using WAITFOR option. You'll need to loop through the trigger table to do this. You will receive a performance penalty due to this.

Tara Kizer
aka tduggan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-01 : 17:01:09

Notice the "IF (SELECT count(1) FROM INSERTED) = 1 begin" ?
the trigger bails out if more than 1 row is inserted.

ok, so still not clear on your question.
>>Because my insert statement is fast I am getting dublicate value. My field size is varchar(8).

are you getting a constraint violation so the transaction is rolling back?
or is the transaction completing but you can see duplicate values in the table? If so which table? (provmstr_new or provcont_new)?

Is the duplicate value(s) the varchar(8) column some other combination of columns?

When you insert multiple rows into your table your trigger will not perform any updates at all, is that intentional?

EDIT:
also, did you run these to see if that join in your update statement could be creating the dupes?


select pvnum, pvuser, count(*) from provmstr_new group by pvnum, pvuser having count(*) > 1
select pvnum, pvuser, count(*) from provcont_new group by pvnum, pvuser having count(*) > 1

Be One with the Optimizer
TG

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-02 : 08:20:16

I try wait delay '00:00:00:500' and it is slow on performace.
I also add a while into my triger:
DECLARE @pvpadn as varchar(8)
WHILE @pvpadn=(SELECT pvpadn from provmstr_new where pvuser='ttt' and pvpadn=@pvpadn)
BEGIN Continie END
SET @pvpadn=.....

I was trying to loop until I have the same value if I don't exit the loop and get new value.
However there is something wrong with my while loop.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources