Creating 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

General SQL Server Forums

New to SQL Server Programming

Creating Trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-12-03 : 14:06:25

Putting a little spin on my last question.

Would like to create a trigger that for insert if the user_cd = 'Z' then I want to update a user_field with its line_no plus the line before it.

Table: orderline
ord_no item_no line_no user_field user_cd
234 ABC 1
234 UZ 2 Z
234 XYZ 3
234 UZ 4 Z

What I want to happen is the following.

ord_no item_no line_no user_field user_cd
234 ABC 1 2
234 UZ 2 2 Z
234 XYZ 3 4
234 UZ 4 4 Z

dios
Starting Member

3 Posts

Posted - 2008-12-03 : 19:17:30

try this

CREATE TRIGGER [trgChangeZ] ON [dbo].[tableABC]
FOR INSERT
AS

declare @char varchar(1)
declare @user_cd as varchar(10)
declare @user_field as varchar(10)
declare @item_no as varchar(10)
declare @line_no as varchar(10)
--I´m no sure what type are the fields

BEGIN
--this line get each row that insert in the table
declare cur3 cursor for select user_cd,user_field,ord_no, item_no, line_no from inserted

begin

open cur3
-- for each row save de values of each field selected in the declare cursor line

fetch next from cur3 into @user_cd, @user_field
@user_cd,@user_field,@ord_no,@item_no,@line_no

while @@FETCH_STATUS = 0
BEGIN

if @user_cd='Z'
BEGIN
update tableABC set user_cd=@user_field
where ord_no=@ord_no and item_no=@item_no and user_field=@user_field
-- the clause WHERE, you have to sure if is the correc row that you want update. i use the field that you send. im no sure.:)

END
-- check other row
fetch next from cur3 into @user_cd,@user_field,@ord_no,@item_no,@line_no
END
close cur3
DEALLOCATE cur3

end
END

i hope this help you
(sorry im learning english

)

Elisamuel Xocoyotzin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 00:27:57

why use cursor?isnt this enough?

CREATE TRIGGER YourTrigger
ON YourTable
AFTER  INSERT
AS
BEGIN
UPDATE t
SET t.UserField= CASE WHEN i.user_cd='Z' THEN i.Line_No ELSE t.Line_No END
FROM YourTable t
INNER JOIN INSERTED i
ON i.Order_No=i.Order_No
AND i.Line_No=t.Line_No-1
END

dios
Starting Member

3 Posts

Posted - 2008-12-04 : 17:40:41

I use cursor because some times i´ve execute a sql query from an aplicacion an this sql has a lot insert and the trigger update a different row. or last row or first row.

But using cursor solve this problem :)

Elisamuel Xocoyotzin

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-05 : 10:42:27

Do NOT put cursors in triggers.
If your code is updating the wrong values it is because it was not written well.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 12:06:59

quote:

Originally posted by visakh16

why use cursor?isnt this enough?

CREATE TRIGGER YourTrigger
ON YourTable
AFTER  INSERT
AS
BEGIN
UPDATE t
SET t.UserField= CASE WHEN i.user_cd='Z' THEN i.Line_No ELSE t.Line_No END
FROM YourTable t
INNER JOIN INSERTED i
ON i.Order_No=i.Order_No
AND i.Line_No=t.Line_No-1
END

That will not work if there is a gap in the sequence. That could be an auto incremented identity column and those often contain gaps.

NB : Edit -- though it obviously works for the data as posted by OP

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 12:11:12

quote:
Originally posted by blindman

Do NOT put cursors in triggers.
If your code is updating the wrong values it is because it was not written well.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Right on! -- dios, if you insert a large set into this table your cursor will make the process crawl.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:24:32

in case of gap, you can do like this


CREATE TRIGGER YourTrigger
ON YourTable
AFTER  INSERT
AS
BEGIN
UPDATE t
SET t.UserField= CASE WHEN i.user_cd='Z' THEN i.Line_No ELSE t.Line_No END
FROM YourTable t
CROSS APPLY(SELECT TOP 1 user_cd,Line_No
            FROM INSERTED
            WHERE Order_No=t.Order_No
            AND Line_No<t.Line_No
            ORDER BY Line_No DESC) i


END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:27:46

and if sql 2000


CREATE TRIGGER YourTrigger
ON YourTable
AFTER  INSERT
AS
BEGIN
UPDATE t
SET t.UserField= CASE WHEN (SELECT TOP 1 user_cd
            FROM INSERTED
            WHERE Order_No=t.Order_No
            AND Line_No<t.Line_No
            ORDER BY Line_No DESC)='Z' THEN (SELECT TOP 1 Line_No
            FROM INSERTED
            WHERE Order_No=t.Order_No
            AND Line_No<t.Line_No
            ORDER BY Line_No DESC) ELSE t.Line_No END
FROM YourTable t
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources