[Newbie] Trigger not working - 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 2005 Forums

Transact-SQL (2005)

[Newbie] Trigger not working

Author

Topic

nbourre
Starting Member

14 Posts

Posted - 2009-01-04 : 00:29:08

Hi,

This is my first post and I need some little help here. I'm a newbie in SQL triggers.

In this problem I have 2 tables one named sectors and the second named sites.

Each site belongs to a sector. The relationship is done with the sites.pkSectorID and the sectors.fkSectorID.
Each sector has a unique code.
Each site has an ID (siteID) which is entered by the user. If the user doesn't provide a siteID a default value is created by concatenating the sector unique code with the sites.pkSiteID.
Each site ID must be unique.

I created the following trigger but all I get is null values. What am I doing wrong?

ALTER TRIGGER tg_new_siteID
ON dbo.sites
AFTER INSERT
AS
	if (select count(*) from inserted where siteID = '') = 1
	begin
		UPDATE sites
		SET sites.siteID = (SELECT sectors.sectorCode FROM sectors WHERE sectors.pkSectorID = (SELECT fkSectorID FROM inserted)) + (SELECT fkSectorID FROM inserted)
		WHERE sites.pkSiteID = (SELECT fkSectorID FROM inserted)
	end

Thanx in advance

Nick
Beginning with something is a good start!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-04 : 03:44:36

[code]
ALTER TRIGGER tg_new_siteID
ON dbo.sites
AFTER INSERT
AS
BEGIN
UPDATE s
SET s.siteID = sr.sectorCode +i.fkSectorID
FROM sites s
JOIN inserted i
ON i.fkSectorID=s.pkSiteID
JOIN sectors sr
ON sr.pkSectorID=i.fkSectorID
WHERE s.siteID = ''
end
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources