[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Set Trigger for Birthdate

Posted on 2010-01-03
15
Medium Priority
?
324 Views
Last Modified: 2012-05-08
I am trying to setup a trigger in SQL.  I know I have some errors below.  My table is tblRegistration.   I am trying to setup a trigger that will turn Combo1's value into a "3"  when the Birthdate of a student is over 18 years of age.

The data in Birthdate column is obviously their birthdate.  How can I set this trigger up properly.
create trigger overage on tblRegistration
after insert, update
as
begin
update tblRegistration
set Combo1 = 3
from tblRegistration
where BirthDate " "
end

Open in new window

0
Comment
Question by:al4629740
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26168593
create trigger overage on tblRegistration
after insert, update
as
begin
update tblRegistration
set Combo1 = 3
from tblRegistration t
join inserted i on t.primarykey = i.primarykey
where BirthDate < dateadd(yy, -18, getdate())
and combo1 <> 3
end
0
 

Author Comment

by:al4629740
ID: 26168601
from tblRegistration t
join inserted i on t.primarykey = i.primarykey

What is "t"  and what is "i"  suppose to be?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26168625
t is alias name for tblRegisteration
i is alias name for table inserted(this table is a system table for inserted items)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:al4629740
ID: 26168646
so is this the way it should be written?
create trigger overage on tblRegistration 
after insert, update 
as 
begin 
update tblRegistration 
set Combo1 = 3 
from tblRegistration t
join inserted i on t.ID = i.ID
where t.Text7 < dateadd(yy, -18, getdate())
and t.combo1 <> 3
end

Open in new window

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26168671
yes it should be correct
0
 

Author Comment

by:al4629740
ID: 26168680
why do I have to use the join statement if I'm not using more than one table?
0
 
LVL 39

Accepted Solution

by:
appari earned 1200 total points
ID: 26169042
instead of trigger you better define combo1 column as a computed column. the reason is when you insert you can test the age and accordingly set the combo1 value to 3 if age is >18. in cases like when data is inserted age is <=18years, combo1 value is set to <>3 when the person reaches 18years age you need to find the record and update it again. if you use computed column you will get the correct combo1 value depending on the age.

try computed column like this,

alter table tblRegistration  add new_Combo1 as case when BirthDate  < dateadd(yy, -18, getdate())  then 3 else 0 end
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26169322
note: the trigger will ONLY fire when the record is actually updated, and NOT when the "birthdate - getdate > 18 years"
you will need a daily script that returns all those that are > 18 "today", but not 18 yesterday ...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26170859
I included the join to the inserted table so that your update statement would only alter the records in the trigger that have been altered.....so, if you originally ran an update for 100 records, your trigger would only update the bday for those 100 records
0
 

Author Comment

by:al4629740
ID: 26172108
Appari,

How do I alter the column Combo1 instead of adding a new column?
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 800 total points
ID: 26172140
you'll have to drop combo1, but once you drop it, you can add it liek this:

alter table tblregistration
add combo1 as case when birthday < dateadd(yy, -18, getdate()) then 3 else 0 end
0
 

Author Comment

by:al4629740
ID: 26172242
then i would lose all the data that is currently in combo1
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26172251
that is correct.  you can't take a data that is physically there and switch it to a computed column....you'd have to add a new field if you don't want to lose the data.
0
 

Author Comment

by:al4629740
ID: 26172334
Can you edit a computed column manually?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26172366
nope, it is computed so you can't physically change any values.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question