If the exists (select null from sysobjects where name='funGetYearVal') 
The drop function funGetYearVal 
Go 
The create function funGetYearVal (@ dateEntry datetime) 
Returns the int 
As 
The begin 
Declare @ result int 
Select @ result=datediff (month, convert (date, @ dateEntry), getdate ())/12 - year of entry 

Return the @ result 
End 

- test 
='the 2001-09-23 - declare @ dateEntry datetime 00:00:00:000' 
- select datediff (month, convert (date, @ dateEntry), getdate ())/12 as yearEntry 

- create a Table 
If the exists (select * from dbo. Sysobjects where id=object_id (N '[dbo]. [] TB) and OBJECTPROPERTY (id, N' IsUserTable ')=1) 
Drop table [dbo] [TB] 
Go 
The create table TB (
Sname varchar (20), 
Gonghao varchar (15), 
RZRQ datetime, 
NJTS as (a case when dbo. FunGetYearVal (RZRQ) & lt;=10 then 5 when dbo. FunGetYearVal (RZRQ) & gt; 10 then 10 else 0 end) 
) 

- data test 
Insert into TB (sname, gonghao RZRQ) 
Select 'zhang', '101001', '1999-10-11 00:00:00:000' union all 
Select 'bill', '1001002', '2010-05-09 00:00:00:000 

Select * from TB 

CodePudding user response:

CodePudding user response:

refer to 6th floor gzb666666 response:

thank you very much, but I have no way in this way, the database table is created automatically by the software, can't use this formula
Can only think of to regularly update the data automatically by the trigger

I said to you speechless, I write, are you don't understand, still can't see,
The annual leave days, is under construction in the data table, produce, or tell the induction log is insert insert in the form of incoming,
This application to be clear, the scene if it is under construction in table, can return to write the annual leave days, the designed formula column, line,

If there is a front-end UI for incoming, just pass it a value, that as a trigger to also go, now, of course, this approach will not be,

CodePudding user response:

Understand it, of course, still want to trigger the form of my database table is created by applying the software can't through the way you write to create table

CodePudding user response:

Don't have to create a table

To create a trigger, should also have permission to modify the table structure,

CodePudding user response:

Table structure will write in a database table, the modified cause application error

CodePudding user response:

Trigger can only be made in the table to update data changes, the query is in, such as update an employee's information today or 10 years, tomorrow may be full for 10 years, query out is not correct,

CodePudding user response:

Estimation is want regular job category, perform an update every day

CodePudding user response:

Hello World reference 11 floor, response:

trigger to update only change data in tables, queries in, such as update an employee's information today or 10 years, tomorrow may be full for 10 years, query out is no longer correct,

Bosses think clearly, pertinent,
Didn't understand the logic of it,, I've always wanted to do tricks on the trigger, the trigger is apparent, because a value changes, and produce a logical action,

The induction time, was produced at what time, I don't understand,
For entry date is dead, but the query the annual leave, can calculate a number of days,

This query is an action and to trigger can pipe insert, update, delete, can also tube had select?

CodePudding user response:

Trigger is not universal,
SQL server, oracle, mysql is not consistent,
But the SQl statement is general,

CodePudding user response:

Triggers have the insert, update, delete triggers,
And you want a time trigger, it belongs to, operations, planning scope,,,