tsql - SQL Server trigger for update inserted row -

-

i want create trigger this: have 2 tables:

table 1:

  • id - integer - primary key
  • status - integer - values: 1 busy , 0 available

table 2:

  • id - integer - primary key - autogenerated
  • id2 - integer - null or value of id table1
  • status - integer - values: 1 working 0 waiting
  • col1 - irrelevant column 1
  • col2 - irrelevant column 2
  • col3 - irrelevant column 3

and when users this

insert table2 (col1, col2, col3) values (val1, val2, val3);

the trigger should check table1 , if finds , available id (status = 0) updates inserted row table2.id2 = table1.id , table2.status becomes 1 otherwise table2.id2 remains null , table2.status becomes 0.

now i've tried many ways , error messages.

this latest attempt:

create trigger mytrigger  on table2 after insert  begin     if not exists (select top 1 id                     table1                    status = 0)     begin         update table2         set status = 0         table2.id = inserted.id     end     else      begin         set status = 1         table2.id = inserted.id          set table.id@ = (select top 1 id                           table1                          status = 0)         table2.id = inserted.id     end end

and errors:

msg 102, level 15, state 1, procedure mytrigger, line 17
incorrect syntax near '='.

line 17: set status = 1

msg 156, level 15, state 1, procedure mytrigger, line 22
incorrect syntax near keyword 'where'.

line 22: table2.id = inserted.id

and if feel generous next thing need create trigger when column deleted table2 sets status of corresponding id in table1 0 (available)

i using sql server 2012

your second statement missing update command:

    set status=1     table2.id= inserted.id     set table.id@ = (select top 1 id                  table1                 status=0)     table2.id= inserted.id

should be

    update table2 set table2.status=1,            table2.id = (select top 1 id                  table1                 status=0)     join inserted     on table2.id= inserted.id;

this should fix errors, however, entire trigger refactored bit cleaner, outside of scope of question.


Comments

Post a Comment

Popular posts from this blog

ios - RestKit 0.20 — CoreData: error: Failed to call designated initializer on NSManagedObject class (again) -

-
this error has shown on many times, seems. prominent answer issue seems restkit 0.20 — coredata: error: failed call designated initializer on nsmanagedobject class however, solution posted doesn't work me. let's start beginning: i want upload file server. want parse response make sure uploaded. if not, try again later. model has boolean variable called nsnumber *senttoserver purpose (in coredata, bool saved nsnumber ) . because don't know whether sending going successful, save entity coredata model first before sending via restkit. when post request successful, server returns json string this: {"id":14,"created":true} notice that, of course, id not set on coredata model yet, both id , created new informations model. here's model: @interface recording : nsmanagedobject @property (nonatomic, retain) nsstring * audiofilepath; @property (nonatomic, retain) nsdate * createdat; @property (nonatomic, retain) nsstring * deviceid; @...
Read more

laravel - PDOException in Connector.php line 55: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) -

-
my .env: app_env=local app_debug=true app_key=myappkey app_url=http://localhost db_connection=mysql db_host=localhost db_port=3306 db_database=gestiontaller db_username=root db_password='root' cache_driver=file session_driver=file queue_driver=sync redis_host=127.0.0.1 redis_password=null redis_port=6379 mail_driver=smtp mail_host=mailtrap.io mail_port=2525 mail_username=null mail_password=null mail_encryption=null i've tried password ", ' , without, restarting server every time. i've tried changing localhost 172.0.0.1. yes, password correct, running mysql -u root -p introducing password root in prompt can go mysql. users table: +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | | root1 | localhost | +------------------+-----------+ i've added homestead.yaml this: database_port: 3306 ...
Read more

c# - How to display vlc video from small screen to another vlc big screen "vlcplugin21" -

-
i'm trying display video in big screen when click on button "view video" after loaded windows in small screen via vlc player "vlcplugin21" in windows form application the following code load video windows , display via vlc player: public void readvlc1() { ofd.filter = "all files (*.*) | *.*"; if (ofd.showdialog() == dialogresult.ok) { vlc1.playlist.items.clear(); vlc1.playlist.add("file:///" + ofd.filename,ofd.safefilename, null); txt_videoname.text = ofd.safefilename; } } i want click on button example "view video in full screen" , display same video big vlc screen. any help??
Read more