Need Trigger to AutoIncriment a numeric column

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 2005 Forums

Transact-SQL (2005)

Need Trigger to AutoIncriment a numeric column

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 10:14:45

I have a column that I need to auto increment each time a record is inserted, but unlike a straight auto inc column this needs to increment within a subset of values, i.e. given Columns W-X-Y-Z, where W is the PK, I want to increment column Z as follows:

W-X-Y-Z
------
1-1-A-1
2-1-A-2
3-1-B-1
4-1-B-2
5-2-A-1
6-2-A-2
7-2-A-3
8-2-B-1
9-2-B-2

I tried the code below, but it doesn't work because the subquery in the WHERE clause returns multiple records.

ALTER TRIGGER [dbo].[set_col_order]
ON [dbo].[DataDicts_Typed]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;

UPDATE DataDicts_Typed
SET typed_col_order =
(SELECT MAX(ISNULL(ddt.typed_col_order, 0))+1
FROM DataDicts_Typed ddt
INNER JOIN inserted i ON i.typed_dict_id = ddt.typed_dict_id
WHERE ddt.source_spec_id = i.source_spec_id AND ddt.typed_container = i.typed_container)
WHERE typed_dict_id = (SELECT typed_dict_id FROM inserted)

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 10:21:38

[code]
ALTER TRIGGER [dbo].[set_col_order]
ON [dbo].[DataDicts_Typed]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;

UPDATE t
SET t.Z=COALESCE(t1.Cnt,0) + 1
FROM Table t
INNER JOIN INSERTED i
ON i.X = t.X
AND i.Y=t.Y
OUTER APPLY (SELECT COUNT(*) AS Cnt FROM Table WHERE X=t.X AND Y=t.Y AND Z IS NOT NULL) t1
WHERE t.Z IS NULL
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 10:53:38

quote:

Originally posted by visakh16


ALTER TRIGGER [dbo].[set_col_order] 
ON [dbo].[DataDicts_Typed]
FOR INSERT
AS 
BEGIN
SET NOCOUNT ON;

UPDATE t
SET t.Z=COALESCE(t1.Cnt,0) + 1
FROM Table t
INNER JOIN INSERTED i
ON i.X = t.X
AND i.Y=t.Y
OUTER APPLY (SELECT COUNT(*) AS Cnt FROM Table WHERE X=t.X AND Y=t.Y AND Z IS NOT NULL) t1
WHERE t.Z IS NULL
END

Close, but not quite.

When done with "AND Z IS NOT NULL" I get a value of 1 for every row.

When done without "AND Z IS NOT NULL" I get the max count for x&y +1 in every row, i.e. the results look like:

W-X-Y-Z
------
1-1-A-2
2-1-A-2
3-1-B-2
4-1-B-2
5-2-A-3
6-2-A-3
7-2-A-3
8-2-B-2
9-2-B-2

Looks like the right dirction though, I'm still working on it, thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 11:22:20

do you have any unique valued column in your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 11:30:52

quote:
Originally posted by visakh16

do you have any unique valued column in your table?

Yes, I was trying something with ranking, but its not working either. This was my latest attempt:

	UPDATE ddt
	SET ddt.typed_col_order = rn.col_order
	FROM DataDicts_Typed ddt
		INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY i.typed_dict_id) +1 as col_order, i.typed_dict_id
		FROM inserted i
		WHERE ddt.source_spec_id = i.source_spec_id AND ddt.typed_container = i.typed_container) rn ON rn.typed_dict_id = ddt.typed_dict_id
	WHERE ddt.typed_col_order IS NULL

typed_dict_id is the auto inc key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 11:42:04

[code]
ALTER TRIGGER [dbo].[set_col_order]
ON [dbo].[DataDicts_Typed]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;

UPDATE t
SET t.Z=COALESCE(t1.Cnt,0) + 1
FROM Table t
INNER JOIN INSERTED i
ON i.X = t.X
AND i.Y=t.Y
OUTER APPLY (SELECT COUNT(*) AS Cnt FROM Table WHERE X=t.X AND Y=t.Y AND Z IS NOT NULL AND typed_dict_id < t.typed_dict_id) t1
WHERE t.Z IS NULL
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources