April 29, 2012 at 11:57 pm
Hi all,
I am new to tsql .i need to write trigger based on source table and rules. My source table and rules is given below
CUSTOMER_SOURCE
cust_idcust_namecust_dobcust_citystate_idcust_phonecust_emailcreated_dtmodified_dt
1234Tim D2/2/1988Austin20048595098tim@gmail.com3/2/2011
1235Bill P5/3/1980New York30046748898bill_p@gmail.com4/2/2011
1278John Q6/13/1988Cheanni40084930393 john@gmail.com5/5/2011
1279John Black6/13/1984Cheanni40084930356john_b@gmail.com5/6/2011
RULES:
If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18
when user inserted a record track the created dt as system date
When any modication happens on a customer row, update the modified date based on the system date.
can any one help on this....
April 30, 2012 at 12:49 am
RULES:
If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18
For the above, assuming no INSERT should occur if they're not 18, use an INSTEAD OF trigger.
when user inserted a record track the created dt as system date
No need for a trigger, use a DEFAULT constraint assigning GETDATE()
When any modication happens on a customer row, update the modified date based on the system date.
This can also be done in INSTEAD OF but could also be done by using GETDATE() as the value for that column on the INSERT.
If you need more specifics, you'll need to post some DDL and sample INSERTs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 30, 2012 at 1:14 am
From what I understand, the first two requirements are not Trigger requirements at all. You can do the following for your requirements:
1. If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18:
For this you should use "CHECK" constraint on .Learn more about "CHECK" here[/url]
2. When user inserted a record track the created dt as system date:
For this you should use "DEFAULT" constraint. Learn more about "DEFAULT" here
3. When any modication happens on a customer row, update the modified date based on the system date:
For this you can use an Update trigger on your table. The code for the trigger is given below.
CREATE Trigger My_Trigger On CUSTOMER_SOURCE
After Update
As
Update CUSTOMER_SOURCE
Set created_dt = GetDate()
Where cust_id in (Select cust_id From Inserted);
April 30, 2012 at 3:31 am
vinu512 (4/30/2012)
From what I understand, the first two requirements are not Trigger requirements at all. You can do the following for your requirements:1. If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18:
For this you should use "CHECK" constraint on .Learn more about "CHECK" here[/url]
The only problem with the CHECK constraint is that you need to handle the error it throws in the UI. With the trigger approach, you can silently remove it from the INSERTed records. This is quite useful if you're loading records in a batch.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 30, 2012 at 4:05 am
dwain.c (4/30/2012)
vinu512 (4/30/2012)
From what I understand, the first two requirements are not Trigger requirements at all. You can do the following for your requirements:1. If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18:
For this you should use "CHECK" constraint on .Learn more about "CHECK" here[/url]
The only problem with the CHECK constraint is that you need to handle the error it throws in the UI. With the trigger approach, you can silently remove it from the INSERTed records. This is quite useful if you're loading records in a batch.
You are right Dwain. Inserting as a batch is better done with a Trigger. But, may be the OP does want the error to be thrown if the user is below 18 years of age.
April 30, 2012 at 4:39 am
vinu512 (4/30/2012)
dwain.c (4/30/2012)
vinu512 (4/30/2012)
From what I understand, the first two requirements are not Trigger requirements at all. You can do the following for your requirements:1. If there is no @ symbol exists in the email ID, reject that record
customer age should be > 18:
For this you should use "CHECK" constraint on .Learn more about "CHECK" here[/url]
The only problem with the CHECK constraint is that you need to handle the error it throws in the UI. With the trigger approach, you can silently remove it from the INSERTed records. This is quite useful if you're loading records in a batch.
You are right Dwain. Inserting as a batch is better done with a Trigger. But, may be the OP does want the error to be thrown if the user is below 18 years of age.
Agreed that we just don't know. When thinking about triggers though, I'm always thinking of bulk inserts.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply