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 2008 Forums
 Transact-SQL (2008)
 how to create an insert/update trigger question

Author  Topic 

Darenr
Starting Member

1 Post

Posted - 2014-06-11 : 10:40:55
how would i create an trigger that fires if table x has a new row added but I need it to update table y?

i have tried the following:


this throws an error on the for

ALTER TRIGGER Alabama_ID_UPDATE_USERBRDG_ON_NEW_BRIDGE ON STRUCTURE_UNIT.STRUNITKEY
AFTER INSERT , UPDATE
AS
FOR EACH ROW



--IF EXISTS(SELECT *
-- FROM INSPEVNT
-- JOIN INSERTED ON
-- INSPEVNT.BRKEY = INSERTED.BRKEY
-- WHERE INSPEVNT.BRKEY = INSERTED.BRKEY)


BEGIN
--SET NOCOUNT ON
--IF (UPDATE(STRUNITKEY))
BEGIN
DECLARE @BRKEY VARCHAR(15)
SET @BRKEY = (SELECT BRKEY FROM inserted)
DECLARE @ALABAMA_ID VARCHAR(20)
SET @ALABAMA_ID = (SELECT ALABAMA_ID FROM USERBRDG WHERE USERBRDG.BRKEY = @BRKEY)
BEGIN
IF(@ALABAMA_ID IS NULL)
SET @ALABAMA_ID = ('XXXXXXXXXXXXXXXXXXX')
END
UPDATE USERBRDG SET USERBRDG.ALABAMA_ID = @ALABAMA_ID WHERE USERBRDG.BRKEY = @BRKEY
END
END

GO


thanks



sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-11 : 11:44:27
I don't think that is valid TSQL, its ok if you remove that line, you may need a cursor for your each row.

We are the creators of our own reality!
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-11 : 12:42:27
The FOR EACH ROW isn't valid.
quote:
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
assembly_name.class_name.method_name


Also, you should learn to work in sets. The path you are going down will only work, properly, with singleton inserts. If you are going to use a trigger, which this seems a dubious use of one, it should be able to handle multi-row DML statements.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 12:47:52
To expand on Lamprey's reply, please check out this Trigger Tip: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-11 : 14:01:16
[code]

ALTER TRIGGER Alabama_ID_UPDATE_USERBRDG_ON_NEW_BRIDGE
ON STRUCTURE_UNIT.STRUNITKEY
AFTER INSERT , UPDATE
AS
SET NOCOUNT ON;

UPDATE ub
SET ub.ALABAMA_ID = 'XXXXXXXXXXXXXXXXXXX'
FROM USERBRDG ub
INNER JOIN inserted i ON
i.BRKEY = ub.BRKEY
WHERE
ub.ALABAMA_ID IS NULL

GO

[/code]
Go to Top of Page
   

- Advertisement -