sql - how to create trigger to split datetime into date -

why trigger not work ? try create trigger insert single date datetime.

example :

when insert datetime, trigger insert date, month, year, dayname, age in other column.

the datetime, date month, year, dayname, age in same table.

  ╔══════════════════════════════╦══════╦═══════╦══════╦═════════╦══════════╗   ║           datetime           ║ date ║ month ║ year ║ dayname ║ age      ║   ╠══════════════════════════════╬══════╬═══════╬══════╬═════════╬══════════╣   ║ april, 06 1982 00:00:00+0000 ║    4 ║     6 ║ 1982 ║ friday  ║       31 ║   ╚══════════════════════════════╩══════╩═══════╩══════╩═════════╩══════════╝  create trigger tgl     on penduduk     insert         declare     @mydate char(11)     select @mydate=tgl_lahir inserted     insert penduduk(tgl) values (datename(day,@mydate)) 

when try code above got message

msg 2714, level 16, state 2, procedure tgl, line 8
there object named 'tgl' in database.

should create column date, month year, dayname , age before create trigger ?

first of all: much easier define computed columns , let sql server handle updating needed automatically:

alter table dbo.penduduk    add dateyear year(datetimecolumn) persisted,        datemonth month(datetimecolumn) persisted,        dateday day(datetimecolumn) persisted 

and on.

secondly, if insist on using trigger - yes must create columns before hand! otherwise, you'll attempt create same columns on , on again - hence error. also: do when datetimecolumn updated? you'll need second trigger that....

also: need aware trigger in sql server run once per statement - , not once per row! inserted pseudo table can contain multiple rows, need code accordingly! don't select value table - after all, have 100 rows in table! row selecting from??

create columns in table before first run of app:

alter table dbo.penduduk    add dateday int, datemonth int, dateyear int   etc. 

then create trigger this:

create trigger tgl on dbo.penduduk insert     update dbo.penduduk     set dateday = day(i.datetimecolumn),         datemonth = month(i.datetimecolumn),         dateyear = year(i.datetimecolumn)     inserted     i.primarykeycolumn = dbo.penduduk.primarykeycolumn