March 5, 2008 at 4:01 pm
I'm using a Database called COMPANY and witihn this I have a table called Works_on.
I'm looking to create a trigger for update on Works_on that if the Hours coloum changes it MUST NOT be reduced and also that it can't be increase by more than 5%
Any idea how I can code this??
March 6, 2008 at 8:24 am
I think you should look at implementing steps in your application code to prevent "bad" data from ever getting into your database to begin with. Performing rollbacks from within triggers really does a great job of adding overhead to your transactions, impacting performance in the process.
Can you give us some explanation on why you wish to do this within the database, post-update? Is it because the application code is in a state where it can not be altered? If so, then you may not have a choice.
If you must do this in the db then my first suggestion is to review Books Online for INSTEAD OF TRIGGERS and ROLLBACKS. If you still have questions please post back.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 9:41 am
Im doing an assignment for college and therefore performance etc is not really an issue for me. I just have to create the trigger to show what happens if the user attempts to do any of the above mentioned steps
March 6, 2008 at 10:18 am
Are you familiar with the INSERTED and DELETED tables?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 10:39 am
Hi Tim,
Thanks for the responce. All I'm really looking for is the code to implement the trigger. Im not sure how to phyically code it.
I've setup stored procedures and views recently but not sure where to start with triggers especially triggers with 2 parts
The trigger i want to setup is a trigger for update on Works_on - if the hours coloum changes it MUST NOT be reduced and not increased by MORE than 5%
March 6, 2008 at 2:53 pm
The reason I asked about the INSERTED and DELETED tables is that you'll query them for the value you just updated. The values from the last transaction are stored in these "virtual" tables and they are flushed upon the next transaction. Keep in mind that an update is simply a chained delete and insert so your before and after values will be in these tables, respectively. Join them on their primary key or a unique key if a PK does not exist. If the difference is outside the threshold you're testing for then undo the transaction. Since the transaction has already completed at this point you will not be able to do a ROLLBACK TRANSACTION with an AFTER trigger, that is why I suggested looking at the INSTEAD OF trigger.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 6, 2008 at 3:06 pm
Im forced to just use the TRIGGER for the question im being asked
March 7, 2008 at 3:52 am
Mikebyrne000 (3/6/2008)
Im forced to just use the TRIGGER for the question im being asked
TRIGGER is fine - that's both types. You declare it as a TRIGGER usually 'FOR UPDATE, INSERT, DELETE' ,whereas an 'INSTEAD OF' trigger is still declared as a TRIGGER, but has 'INSTEAD OF UPDATE, INSERT, DELETE. Basically you then get passed the data (in the Inserted and Deleted tables as previously mentioned) and your trigger becomes entirely responsible for doing the update/insert/delete. Note that these tables can contain multiple rows, so your operations will need to work on sets of data. To do what you need to do it sounds like you need
CREATE TRIGGER enforceWorkingHours ON tblWhatever
INSTEAD OF UPDATE
AS
Update w
SET w.col1 = i.col1,
w.col2 = i.col2,
etc...
workingHours = i.workingHours
FROM Inserted i
INNER JOIN tblWhatever w
ON w.primaryKeyCol = i.primaryKeyCol (Inserted and deleted tables have the same cols as the triggered table)
INNER JOIN Deleted d
ON ...
WHERE -- all the actual control happens in here
-- only do the update where your criteria are matched, otherwise don't
-- i.e. i.workingHours (comparator) d.workingHours >= (value)
-- and other conditions as required
Seeing as its an assignment I've kept the details deliberately vague-ish
You could then have another update or insert which will put a message in a table somewhere for the opposite where clause, i.e. for rows where your constraints have been broken. I don't like INSTEAD OF triggers really because if your data slips through the net as it were (i.e. you don't have a where clause match) then nothing will get updated. However, they're the only way to control values on the table that's actually triggering the change. A 'FOR' trigger cannot (correct me if I'm wrong someone) affect the same table for which the trigger has fired.
Hope this helps
-- Kev
-------------------------------Oh no!
March 7, 2008 at 4:01 am
Hi Kev,
Its not an assignment its just some lab work Im doing today at 1. Thanks for the pointers though. It makes things a little clearer in my head
Two question
1) I'm not clear as too what SET w.col1 = i.col1,
w.col2 = i.col2, does and how to apply same to my table
2) How to I actually write the WHERE statement for my problem which has 2 scenarios. Do i need to write 2 seperate statements??
March 7, 2008 at 4:59 am
Mikebyrne000 (3/7/2008)
Two question1) I'm not clear as too what SET w.col1 = i.col1,
w.col2 = i.col2, does and how to apply same to my table
2) How to I actually write the WHERE statement for my problem which has 2 scenarios. Do i need to write 2 seperate statements??
1. The SET statement is required because you need to explicitly do all the work that the UPDATE *would* have done had you not interrupted it with the INSTEAD OF UPDATE trigger. w.col1 = i.col1 is pulling the values from the 'Inserted' table into your table, overwriting the old values (which will also be found in the 'Deleted' table). Substitute your column names for col1, col2 etc. As someone said above, in a trigger you have access to two tables, Inserted and Deleted. For an INSERT operation, Inserted will contain all the values and Deleted will contain nothing. For a DELETE operation, this will be reversed, and for an UPDATE the old values will be in Deleted (because they will logically be removed) and the new values will be in 'Inserted'.
In order for you to entirely simulate the update in your trigger, you would simply set yourtable.column1 = Inserted.column1, yourtable.column2 = Inserted.column2 etc. All that you're affecting here is the criteria, so you'll need to do the entire update with the values from 'Inserted' subject to the criteria in the WHERE.
2. Your WHERE statement will simply contain two conditions 'logical AND'ed together. One that says that the new value of workingHours must be equal to or greater than the old value, and a second that says that the new value of working hours must be no more than 5% higher than the old value of working hours.
To do the comparisons you will use the Inserted and Deleted tables again.
UPDATE.... (as above)
SET ... (as above)
FROM.... (as above)
WHERE Inserted.WorkingHours is greater than or equal to Deleted.WorkingHours
AND the difference between Inserted.WorkingHours and Deleted.WorkingHours is no greater than 5% of Deleted.WorkingHours.
You COULD do this in a single expression (i.e. val1 - val2 BETWEEN 0 and (5% of val2)), but for readability including the two individually is easier.
If you need to put messages somewhere to report your two scenario failures individually, then you'll need to use two separate INSERT statements (into some sort of logging table) each with their own WHERE clause, again using Inserted and Deleted, but with positive tests for the two failures on them. I.e. (for the 'Not less than...' criterium)
INSERT INTO ErrorMessages (message)
SELECT 'Working Hours reduction not allowed'
FROM Inserted i INNER JOIN Deleted d
ON i.key = d.key
WHERE i.WorkingHours < d.WorkingHours
These will be after and entirely independent of the main UPDATE though.
-- Kev
-------------------------------Oh no!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply