Trigger creation - help please ;-) 1

[MikeBronner]

Hi guys,
I'm totaly new to creating triggers in SQL Server. I was wondering if you could help me out here.

Here's my problem: I receive input through an input statement containing 5 columns (e.g. A through H).

Before the data is saved to the table, I would like the content of column E manipulated, only if column D = C1, column E = C2, and column F = C3 (where C1 - C3 are criteria).

Column A through C represent the primary key columns.

Any ideas on how to write a trigger to accomplish this?

Thanks! Take Care,
Mike

 

[malexanian]

Two questions:

1. Which version of SQL Server are you using? 7.0 and before have "FOR" triggers only (after the data is saved). 2000 has "INSTEAD OF" triggers (before the data is saved).

2. Are the criteria being passed in? How are you getting the criteria?

 

[MikeBronner]

I am using SQL Server 2000. The criteria are other columns in the input.

Thanks! ;-) Take Care,
Mike

 

[malexanian]

I wish CHECK CONSTRAINTs could do this, but they can't, so:

CREATE TRIGGER <trigger_name> <-- must be unique to db
ON <table_name>
FOR INSERT, UPDATE <-- Actions that will trip trigger
Also, note the &quot;FOR&quot; word.
AS
UPDATE <table_name>
SET t.D = t.C1, E = C2, F = C3
WHERE
<table_name> t JOIN
inserted i ON t.A=i.A and t.B=i.B and t.C=i.C

That's it.

If you have questions on triggers or on the &quot;inserted&quot; table, check Books Online.

 

[MikeBronner]

Cool! Thanks ;-)

One question though: where do you check for the constraints? The trigger should only run of all constraints are true, not on every insert.

Thanks ;-) Take Care,
Mike

 

[MikeBronner]

Also, I'm getting an error saying:
Line 8, incorrect syntax near 't'.

If I remove the t, it doesn't like the join. Take Care,
Mike

 

[MikeBronner]

Here's the actual trigger I have so far. Would this work?

CREATE TRIGGER trg_userruns_pocketformat
ON Userruns
FOR INSERT
AS
DECLARE @date DATETIME
DECLARE @time DATETIME
DECLARE @sorter VARCHAR(20)
SELECT @date = [Start Date], @time = [Start Time], @sorter = Sorter FROM Inserted
UPDATE Userruns
SET [User field 5] = (([User field 5] / 10) -1) * 6 + ([User field 5] % 10) -1
WHERE
Userruns.[Start Date] = @date
AND [Start Time] = @time
AND Sorter = @sorter
AND Application = 'CHX2000'
AND [User field 1] = '2'

Thanks ;-) Take Care,
Mike

 

[malexanian]

That should work just fine (see note 2). Three notes:

The 't' in my original text was merely an alias for the table. You shouldn't get an error on that... &quot;SELECT t.field1 FROM tblTable t&quot; should be fine.

The variables in the trigger are inefficient. It would be a lot better if you could simply join it to the 'inserted' table. Plus, the inserted table also covers the case where you do a batch insert. In this case, the records are inserted into the table and copies of all of these records are in 'inserted'. That's why the inner join of the table with inserted on the primary key fields should work. The variables only work if there is only one record inserted.

The trigger trips only after constraints are passed. Note that FOR triggers trip AFTER the INSERT/UPDATE/DELETE. The operations don't happen unless the constraints are passed in the first place. I don't recall the order the constraints are passed... I think it's NOT NULL, then CHECK constraints, then PRIMARY KEYs and UNIQUE indexes, and finally FOREIGN KEYs. I could be wrong. In any case, the trigger happens after these. Note that an INSTEAD OF trigger fires 'instead of' the operation, which means that constraints aren't processed on the data passed.

 

[MikeBronner]

I have rewritten the trigger, and replaced WHERE with FROM in the update clause. That checked out ok, at least as far as syntax is concerned. Would this trigger do what I want?:

CREATE TRIGGER trg_userruns_pocketformat
ON Userruns
FOR INSERT
AS
UPDATE t
SET t.[User field 5] = (([User field 5] / 10) - 1) * 6 +
(t.[User field 5] % 10) - 1
FROM Userruns t
INNER JOIN Inserted i ON
i.{User field 1] = '2' AND
i.Application = 'CHX2000' AND
i.Operator LIKE 'P%' AND
t.[Start Date] = i.[Start Date] AND
t.[Start Time] = i.[Start Time] AND
t.Sorter = i.Sorter

Thanks again ;-) Take Care,
Mike

 

[malexanian]

Move the non-join criteria into the WHERE clause. And don't mind my weird tab structure; that's my preferred way of displaying from clauses.


CREATE TRIGGER trg_userruns_pocketformat
ON Userruns
FOR INSERT
AS
UPDATE t
SET t.[User field 5] = ((t.[User field 5] / 10) - 1) * 6 +
(t.[User field 5] % 10) - 1
FROM
Userruns t
INNER JOIN
Inserted i
ON
t.[Start Date] = i.[Start Date] AND
t.[Start Time] = i.[Start Time] AND
t.Sorter = i.Sorter
WHERE
i.{User field 1] = '2' AND
i.Application = 'CHX2000' AND
i.Operator LIKE 'P%' AND

 

[MikeBronner]

Thanks so much for your patience with guiding me through this process ;-)

I implimented the trigger and it appears to be working ;-)

Woot! Take Care,
Mike