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.
| Author |
Topic |
|
kapilarya
Yak Posting Veteran
86 Posts |
 Posted - 2005-07-31 : 04:22:55
|
| In sql server all triggers are after triggers by defult.. that is executed after the execution of up insert, update or delete statement.. but how to specity before trigger, that is trigger to be executed first and then the insert,update or delete statment |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-31 : 04:42:18
|
| Don't think you can, but there is an INSTEAD OF TRIGGER [SQL2k] which will fire instead of the actual insert - you then have to actually make the INSERT (in the trigger), so it is likely to need adjusting if new columns are added to the table etc.Kristen |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-31 : 09:44:00
|
| I know this won't help your immediate problem but...That is one of the reasons for having users and applications access the database through stored procedures only and not allowing direct table access. You can then manage all business rules, validations, etc. in the stored procedure before the actual inserts, updates, and deletes are performed.Can you use a regular (after) trigger and then either rollback or update based on whatever you wanted to check for?Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-31 : 11:34:29
|
| I don't know the nature of kapilarya's problem, but I struggle when I want to have a NOT NULL column that is "derived" at INSERT, and I want to use a trigger instead of an SProc.Kristen |
|
|
|
kapilarya
Yak Posting Veteran
86 Posts |
Posted - 2005-08-01 : 05:04:57
|
| Hello Kristen,My problem is before inserting into a certain table i wnt to check some data from other table and if it statisfy then only i want to perform insert action. if i use normal insert trigger then it insert record first then checking done then if conditions doesnot get setisfied then i have to rollback the operation. actually the problem is the table in which i have to insert has a identity column, and identity value get incremented on every insert.Kapil Arya |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-08-01 : 05:18:07
|
this is not a job for a trigger if you ask me...this a business logic that should happen before the actual insert and you have to code it.if you're not using sprocs (highly recommened) then build the statement and execute it in one go...Go with the flow & have fun! Else fight the flow  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-01 : 08:12:27
|
| Might depend on where the data is coming from - if its some rubbish extrnal application then a TRIGGER might be the only way.If its your own application then Spirit's right, sort the application out to do it in a better way - preferably through an SProc.You need to live with IDENTITY numbers having gaps. If there is a ROLLBACK (which might be for deadlock, or some other "act of God!" reason) then IDENTITY number will still advance.Kristen |
|
|
|
|
|
|
|
|
TRIGGERS