March 12, 2008 at 2:40 am
Im having real trouble trying the right the code for a new trigger. What I want to do i write a trigger for my Works_on table so that if the "Hours" colum changes it MUST NOT be reduced AND NOT INCREASED by 5%
I understand that I have to simulate the action first before allowing an update to happen so I assume in my case it would be
Works_on.hours = inserted.Hours
Works_on.hours = deleted.Hours
Now I understand the structure of what I have to do next
UPDATE
SET
FROM
I'm just not sure how to code it correctly
Im also having a real problem with the WHERE statement
In english it should be:
WHERE inserted.hours is greater than or equal than deleted.hours AND the difference between inserted.hours and deleted.hours is no greater than 5% of deleted.hours
I really dont know how to write the expression in code!!
March 12, 2008 at 3:04 am
Try something like this. Untested. Please test carefully.
CREATE TRIGGER trg_PreventInvalidHours ON works_on AFTER UPDATE AS
IF EXISTS (
SELECT 1 FROM inserted i inner join deleted d on i.<Primary Key Column> = d.<Primary Key Column>
WHERE i.hours<d.hours -- Hours reduced
OR CAST(i.hours-d.hours AS NUMERIC(10,3))/(CAST d.hours AS NUMERIC(10,3)) > 0.05)
BEGIN
Raiserror < Appropriate Error Message >
Rollback Transaction -- undo the update.
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 3:15 am
Would that be the complete code that i need or do i have to adjust it?
Where could I insert an error msg like:
INSERT INTO Errormessages 9message)
SELECT 'Working Hours reduction not allowed!'
From Inserted i INNER JOIN Deleted d
on i.hours = d.key
WHERE i.hours<d.hours
do i replace d.key with d. (the primary key field from works_on) ??
March 12, 2008 at 3:24 am
Mostly complete.
If you want to insert into an errors table, do it right after the rollback transaction. You can also use the raiserror to send an error back to the calling procedure/client.
The join between inserted and deleted needs to be on the primary key.
The inserted table is a virtual table containing the rows affected by the update that shows the new values after the update. The deleted table shows the old values before the update.
If you want to do two different checks and error messages, you can split the exists so that one checks for reduction (the first part of the where) and another checks for the 5% (the second part of the where clause)
If you call rollback in a trigger, the update that fired the trigger is also undone. Hence the trigger as I wrote it will prevent illegal changes, not just log them to a table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 3:30 am
Oh ok, thanks for your help
So in your code
SELECT 1 FROM inserted i inner join deleted d on i.Primary Key Column = d.Primary Key Column
I should just replace "Primary Key Column " with the actual primary key column
In my table there 2 primary keys ESSN and SSN. Does it matter which one i use?
March 12, 2008 at 3:39 am
You can't have 2 primary keys on a table. If you mean it's a 2-column primary key, then you need to use both.
So:
inserted.ESSN = deleted.ESSN AND inserted.SSN = deleted.SSN
The point is to ensure that the rows from deleted and inserted match
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 3:51 am
Sorry my mistake
SSN is the Primary Key while ESSN is a forign key
March 12, 2008 at 4:10 am
Then SSN is what the join needs to be on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 10:32 am
Im getting the following error for the code
CREATE TRIGGER trg_PreventInvalidHours ON Works_On AFTER UPDATE AS
IF EXISTS (SELECT 1 FROM inserted i inner join deleted d
on i.ESSN = d.ESSN WHERE i.hours < d.hours -- Hours Reduced
OR CAST (i.hours-d.hours AS NUMERIC (10,3))/(CAST d.hours AS NUMERIC (10,3))
> 0.05)
BEGIN
Raiserror
Rollback Transaction -- undo the update.
END
Msg 102, Level 15, State 1, Procedure trg_PreventInvalidHours, Line 9
Incorrect syntax near 'd'.
Any idea why?
March 12, 2008 at 12:38 pm
Try this.
CREATE TRIGGER trg_PreventInvalidHours ON Works_On AFTER UPDATE AS
IF EXISTS (SELECT 1 FROM inserted i inner join deleted d
on i.ESSN = d.ESSN WHERE i.hours < d.hours -- Hours Reduced
OR CAST (i.hours-d.hours AS NUMERIC (10,3))/CAST (d.hours AS NUMERIC (10,3))
> 0.05)
BEGIN
Raiserror
Rollback Transaction -- undo the update.
END
March 13, 2008 at 12:52 am
Bracket in the wrong place. This passes a syntax check.
CREATE TRIGGER trg_PreventInvalidHours ON Works_On AFTER UPDATE AS
IF EXISTS (SELECT 1 FROM inserted i inner join deleted d
on i.ESSN = d.ESSN
WHERE i.hours < d.hours -- Hours Reduced
OR (CAST (i.hours-d.hours AS NUMERIC (10,3))/CAST (d.hours AS NUMERIC (10,3)) > 0.05))
BEGIN
Raiserror ('Oops',16,1) -- Change to a more useful error message.
Rollback Transaction -- undo the update.
END
You need to join on SSN, not ESSN if SSN is your primary key.
Also, Raiserror takes parameters. I left them out, but you need to have an error statement and a severity. See Raiserror in BoL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2008 at 2:24 am
No, SSN is now the primary key as I change the structure.
Thanks for you help
March 13, 2008 at 4:07 pm
Im not too sure why we use SELECT 1, could you explain to me what this does??
March 13, 2008 at 4:20 pm
GilaMonster (3/13/2008)
Bracket in the wrong place. This passes a syntax check.
CREATE TRIGGER trg_PreventInvalidHours ON Works_On AFTER UPDATE AS
IF EXISTS (SELECT 1 FROM inserted i inner join deleted d
on i.ESSN = d.ESSN
WHERE i.hours < d.hours -- Hours Reduced
OR (CAST (i.hours-d.hours AS NUMERIC (10,3))/CAST (d.hours AS NUMERIC (10,3)) > 0.05))
BEGIN
Raiserror ('Oops',16,1) -- Change to a more useful error message.
Rollback Transaction -- undo the update.
END
You need to join on SSN, not ESSN if SSN is your primary key.
Also, Raiserror takes parameters. I left them out, but you need to have an error statement and a severity. See Raiserror in BoL
The IF EXISTS is a test for existence. If there are any rows in the join between the inserted and deleted tables, this will return true. In the EXISTS you don't have to reference any columns from the tables in the select.
😎
March 14, 2008 at 8:08 am
Keep in mind that if just 1 row meets your criteria, you will be undoing the entire update.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply