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

General SQL Server Forums

New to SQL Server Programming

Trigger help

Author

Topic

ZrednaZ
Starting Member

1 Post

Posted - 2008-10-27 : 17:41:24

Hi everyone,

This trigger won't run - I just can't figure out why!
I want it to run whenever any event occurs on the table Medarbejder (Employee). In fact, ideally, I want it to run when an event occurs on any one of two tables (eg. AFTER INSERT OR UPDATE OR DELETE ON Medarbejder OR other_table). Can this be done?

CREATE TRIGGER OpdaterLedigeAarsvaerk
AFTER INSERT OR UPDATE OR DELETE OF medid ON Medarbejder

DECLARE
Medarbejderantal number,
Totalaarsvaerk number,
Allokeret number,
Kapacitet number;

BEGIN

SELECT COUNT(*) INTO Medarbejderantal
FROM Medarbejder

SET Totalaarsvaerk = Medarbejderantal * 8

SELECT SUM(allokerettid) INTO Allokeret
FROM MedarbejderProjekt

SET Kapacitet = Totalaarsvaerk - Allokeret

UPDATE LedigeAarsvaerk
SET ledaarsvaerk = Kapacitet
END;

Any help greatly appreciated! If necessary, I can translate the values to English...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-27 : 18:14:39

A trigger can only fire on one table. So if you want it to fire on two tables, then you need two triggers.

For variables, you need to use @: DECLARE @i int SET @i = 1

Could you explain in words what you want the trigger to do because I'm sure you don't need variables in the trigger at all?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 18:19:17

This is a Microsoft SQL Server forum.

CREATE TRIGGER OpdaterLedigeAarsvaerk ON Medarbejder
AFTER INSERT, UPDATE, DELETE 
AS

DECLARE	@Totalaarsvaerk BIGINT,
	@Allokeret BIGINT

SELECT	@Totalaarsvaerk = 8 * COUNT(*)
FROM	Medarbejder

SELECT	@Allokeret = SUM(allokerettid)
FROM	MedarbejderProjekt

UPDATE	LedigeAarsvaerk
SET	ledaarsvaerk = @Totalaarsvaerk - @Allokeret

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources