Applying triggers to sinple sql queries 2

[ja01]

I have the following update query in SQL
update dbo.core
set birthyr = Year(BirthD),
brthmn = Month(BirthD),
brthdy = Day(BirthD)


Now my boss wants me to turn off triggers while this update query is excecuting then turn it back on. I have not used triggers prevously but I want to knwo if someone could review my code I am trying to keep this as simple as possible:
alter table core disable trigger all

update dbo.core
set birthyr = Year(BirthD),
brthmn = Month(BirthD),
brthdy = Day(BirthD)
alter table enable trigger all

My feeling is that it is not that simple and that I may need to see if there is a template in enterprise manager to handle this and perhaps assist me with some coding.

 

[SQLDenis]

that should do it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

But first, why turn off the TRIGGERs while it's running? If the table has triggers, there is normally a reason for them - such as making sure duplicate information isn't entered or to make sure the values are acceptable. They can also be used to keep track of changes - for example, someone changing their age. I don't know what your database is used for, but let's say it's used to determine if someone is eligible for certain discounts. Wouldn't you want to know if someone changed their date of birth to make them eligible for those benefits? I would check what the triggers do and how these changes would/would not be affected by the triggers.

-SQLBill

Posting advice: FAQ481-4875

 

[ja01]

sql Bill thanks for your insight. One more question. The update script with the trigger works. The only issue is that if the brthmn or brthdy is less that 10, the number comes over as a sigle digit number without the 0 (ie I want 7 to come over as 07) any ideas

 

[SQLSister]

update dbo.core
set    birthyr = Year(BirthD),
       brthmn = Month(BirthD),
       brthdy = right(replicate ('0',2) + cast(Day(BirthD)as varchar(2)), 2)

You can follow the logic to do the same for a month. This of course assumes that your field is not a numeric datatype becasue those cannot store leading zeros.

Questions about posting. See faq183-874

 

[SQLBill]

What datatype is the column? INT will ALWAYS drop the leading zero. I'm not sure about other number datatypes.

-SQLBill

Posting advice: FAQ481-4875

 

[ja01]

BirthD is actually a datetime data type. I am trying to take BirthD and segment it into brthyr, brthdy and brthmn which are char datatypes. So it appears that the latest script you gave me needs to convert BirthD from datetime to char so it can be used in the three char datatypes. When I tried your latest script, I did get a data conversion error. I guess my question now is do I need to add a convert statement to the following script:

brthdy = right(replicate ('0',2) + cast(Day(BirthD)as varchar(2)), 2)

 

[SQLDenis]

well obviously this works

select brthdy = right(replicate ('0',2) + cast(Day(getdate())as varchar(2)), 2)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ja01]

sql denis
The code worked. It put a 0 in front of the month. However the result was the number 02. The person's birth month was supposed to be 07. It did the same for the day.
Maybe if I could get an explanation of the code that might help me
ie right(replicate '0',2) does this mean put a leading 0 if the month is a single digit???
+cast ???
Month(getdate() does this mean pull from the corebrthd which is the actual datetime column that has the full date 99/99/9999
corebrthmn = right(replicate ('0',2) + cast
(Month(getdate())as varchar(2)), 2),

 

[SQLDenis]

No (Month(getdate())as varchar(2)), 2),

has to be replaced with

(Month(BirthDas varchar(2)), 2),

I just gave the query so that you could see the result


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/