trigger: update field from query result

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

trigger: update field from query result

Author

Topic

T72
Starting Member

1 Post

Posted - 2012-06-26 : 10:24:03

hi

i'm pretty new to sql scripting... can anyone help me?

i created a new Trigger1 and i need to update Field1 in Table1 with a result coming from a simple query like SELECT TOP 1 Field2 FROM Table2 ORDER BY ranking2 DESC

should be something like:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [triggerName] ON [dbo].[Table1]

FOR INSERT, UPDATE
AS

SELECT id FROM INSERTED

BEGIN

DECLARE @id Varchar(50)
DECLARE @newvalue datetime

SET @newvalue = (SELECT TOP 1 Field2 FROM Table2 WHERE Table2.uuid = @id ORDER BY ranking2 DESC)

UPDATE Table1
SET Field1 = @newvalue
FROM Table2
WHERE uuid = @id

END

---------------------------

TIA, T.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-26 : 10:40:08

something like this:


CREATE TRIGGER [trigger] ON tempdb.dbo.table1
AFTER INSERT, UPDATE
AS
	UPDATE t SET
		[Field1] = f2Lookup.[field2]
	FROM
		tempdb.dbo.table1 AS t
		JOIN INSERTED AS i ON i.[uuid] = t.[uuid]
		CROSS APPLY (
			SELECT TOP 1 [Field2]
			FROM tempdb.dbo.Table2 As t2
			WHERE t2.[uuid] = i.[id]
			ORDER BY [ranking2] DESC
			)
			AS f2Lookup
			
GO

Example:

BEGIN TRAN

USE [tempdb]

CREATE TABLE table1 ([uuid] VARCHAR(50) PRIMARY KEY, [field1] INT, [id] VARCHAR(50), [VALUE] VARCHAR(50))
CREATE TABLE table2 ([uuid] VARCHAR(50), [field2] INT, [ranking2] INT)

INSERT table1([uuid], [field1], [id], [value]) VALUES ('a', 1, 'aaa', 'before')
INSERT table2([uuid], [field2], [ranking2]) VALUES ('aaa', 2, 0), ('aaa', 3, 34)

SELECT * FROM table1
SELECT * FROM table2
GO

CREATE TRIGGER [trigger] ON tempdb.dbo.table1
AFTER INSERT, UPDATE
AS
	UPDATE t SET
		[Field1] = f2Lookup.[field2]
	FROM
		tempdb.dbo.table1 AS t
		JOIN INSERTED AS i ON i.[uuid] = t.[uuid]
		CROSS APPLY (
			SELECT TOP 1 [Field2]
			FROM tempdb.dbo.Table2 As t2
			WHERE t2.[uuid] = i.[id]
			ORDER BY [ranking2] DESC
			)
			AS f2Lookup
			
GO
			

UPDATE table1 SET [VALUE] = 'after'

SELECT * FROM table1
ROLLBACK

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources