Trigger help - 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 2008 Forums

Transact-SQL (2008)

Trigger help

Author

Topic

beemd
Starting Member

14 Posts

Posted - 2013-10-23 : 10:26:37

Just wondering if someone could help with a trigger, i've not used them before:

In Table A i've got phonenumber, laststatustimestamp and laststatus

In Table B i've got phonenumber, tstart and status

When a row is added to B I want to update A setting A.laststatustimestamp = B.start, A.laststatus = B.status WHERE a.phonenumber = b.phonenumber

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 10:48:28

It would be something like this (please test before you use this). Don't you also want to do something if a row in TableB is updated or deleted?

CREATE TRIGGER dbo.TableBTrigger ON dbo.TableB
FOR INSERT 
AS
	UPDATE a SET 
		laststatustimestamp = i.tstart,
		laststatus = i.STATUS
	FROM
		TableA a
		INNER JOIN INSERTED i ON i.phonenumber = a.phonenumber;
		
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:04:43

What about case where status of last record in B was modified to something else?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beemd
Starting Member

14 Posts

Posted - 2013-10-24 : 04:52:58

I've been trying to get this working as per your suggestion, this is what I have:

ALTER TRIGGER [dbo].[UpdateMain] ON [dbo].[communication]
WITH EXECUTE AS 'executive'
AFTER INSERT
AS
UPDATE a SET
a.[LastStatusTimeStamp] = i.[tstart],
a.[LastStatus] = i.[status]
FROM
[dbFax].[dbo].[tblMasterData] a
INNER JOIN INSERTED i ON i.[dest] = a.[faxnumber]
WHERE i.command = 'SEND'

I should mention that dbFax is a different DB on the same server, the "executive" account has db_owner on both DB's

It doesn't seem to work ('tblMasterData' isn't updated, and also when it's enabled no rows get inserted into 'communication' either).

How can I debug it?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 08:57:41

the trigger code doesnt have any obvious errors.Can you show the INSERT code you tried?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 09:02:01

Check again to see if your join conditions are correct. It may be unusual naming convention for the columns, but the join condition does not seem right

INNER JOIN INSERTED i ON i.[dest] = a.[faxnumber]

Does the dest column in the inserted row correspond to faxnumber column in the tbMasterData table? Does tbMasterData table already have that fax number in there?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources