Thursday, May 30, 2013

unix shell scripting

1. What is UNIX?

It is a portable operating system that is designed for both efficient multi-tasking and mult-user functions. Its portability allows it to run on different hardware platforms. It was written is C and lets user do processing and control under a shell.

2. What is Shell?

A shell acts as an interface between the user and the system. As a command interpreter, the shell takes commands and sets them up for execution.

3. What are the key features of the Korn Shell?

- history mechanism with built-in editor that simulates emacs or vi
- built-in integer arithmetic
- string manipulation capabilities
- command aliasing
- arrays
- job control

4. What are some common shells and what are their indicators?

sh – Bourne shell
csh – C SHell
bash – Bourne Again Shell
tcsh – enhanced C Shell
zsh – Z SHell
ksh – Korn SHell

5. What is shell scripting in UNIX?

Shell scripting is used to program command line of an operating system. Shell Scripting is also used to program the shell which is the base for any operating system. Shell scripts often refer to programming UNIX. Shell scripting is mostly used to program operating systems of windows, UNIX,  Apple etc. Also this script is used by companies to develop their own operating system with their own features.

6. What is a typical syntax being followed when issuing commands in shell?

Typical command syntax under the UNIX shell follows the format:
Command [-argument] [-argument] [--argument] [file]

7. What is command substitution?

Command substitution is one of the steps being performed every time commands are processed by the shell. Commands that are enclosed in backquotes are executed by the shell. This will then replace the standard output of the command and displayed on the command line.

8. What is a directory?

Every file is assigned to a directory. A directory is a specialized form of file that maintains a list of all files in it.

9. What is history command in UNIX?

We use history command along with grep command in unix to find any relevant command you have already executed.

10. How do you copy file from one host to other?

By using "scp" command. You can also use rsync command to answer this UNIX interview question or even sftp would be ok.

11. How do you find which process is taking how much CPU?

By using "top" command in UNIX.

12. How do you check how much space left in current drive?

By using "df" command in UNIX. For example "df -h ." will list how full your current drive is.

13. How do you know if a remote host is alive or not?

You can check these by using either ping or telnet command in UNIX.

14. How will you run a process in background? How will you bring that into foreground and how will you kill that process?

For running a process in background use "&" in command line. For bringing it back in foreground use command "fg jobid" and for getting job id you use command "jobs", for killing that process find PID and use kill -9 PID command.

15. How will you find which operating system your system is running on in UNIX?

By using command "uname -a" in UNIX

16. What is the command to list all the links from a directory?

You can answer command like: ls -lrt | grep "^l"

17. How will you create a read-only file in your home directory?

You need to create a file and change its parameter to read-only by using chmod command you can also change your umask to create read only file.
touch file
chmod 400 file

18. What is the difference between Swapping and Paging?

Swapping:Whole process is moved from the swap device to the main memory for execution. Process size must be less than or equal to the available main memory. It is easier to implementation and overhead to the system. Swapping systems does not handle the memory more flexibly as compared to the paging systems.
Paging:Only the required memory pages are moved to main memory from the swap device for execution. Process size does not matter. Gives the concept of the virtual memory. It provides greater flexibility in mapping the virtual address space into the physical memory of the machine. Allows more number of processes to fit in the main memory simultaneously. Allows the greater process size than the available physical memory. Demand paging systems handle the memory more flexibly.

19. What are filters?

The term Filter is often used to refer to any program that can take input from standard input, perform some operation on that input, and write the results to standard output. A Filter is also any program that can be used between two other programs in a pipeline.

20. Differentiate multiuser from multitask.

Multiuser means that more than one person can use the computer at the same time. Multitask means that even a single user can have the computer work on more than one task or program at the same time.

21. What is inode?

An inode is an entry created on a section of the disk set aside for a file system. The inode contains nearly all there is to know about a file, which includes the location on the disk where the file starts, the size of the file, when the file was last used, when the file was last changed, what the various read, write and execute permissions are, who owns the file, and other information



Basic shell scripting questions
  • How do you find out what’s your shell? – echo $SHELL
  • What’s the command to find out today’s date? – date
  • What’s the command to find out users on the system? – who
  • How do you find out the current directory you’re in? – pwd
  • How do you remove a file? – rm
  • How do you remove a – rm -rf
  • How do you find out your own username? – whoami
  • How do you send a mail message to somebody? – mail somebody@techinterviews.com -s ‘Your subject’ -c ‘cc@techinterviews.com‘
  • How do you count words, lines and characters in a file? – wc
  • How do you search for a string inside a given file? – grep string filename
  • How do you search for a string inside a directory? – grep string *
  • How do you search for a string in a directory with the subdirectories recursed? – grep -r string *
  • What are PIDs? – They are process IDs given to processes. A PID can vary from 0 to 65535.
  • How do you list currently running process? – ps
  • How do you stop a process? – kill pid
  • How do you find out about all running processes? – ps -ag
  • How do you stop all the processes, except the shell window? – kill 0
  • How do you fire a process in the background? – ./process-name &
  • How do you refer to the arguments passed to a shell script? – $1, $2 and so on. $0 is your script name.
  • What’s the conditional statement in shell scripting? – if {condition} then … fi
  • How do you do number comparison in shell scripts? – -eq, -ne, -lt, -le, -gt, -ge
  • How do you test for file properties in shell scripts? – -s filename tells you if the file is not empty, -f filename tells you whether the argument is a file, and not a directory, -d filename tests if the argument is a directory, and not a file, -w filename tests for writeability, -r filename tests for readability, -x filename tests for executability
  • How do you do Boolean logic operators in shell scripting? – ! tests for logical not, -a tests for logical and, and -o tests for logical or.
  • How do you find out the number of arguments passed to the shell script? – $#
  • What’s a way to do multilevel if-else’s in shell scripting? – if {condition} then {statement} elif {condition} {statement} fi
  • How do you write a for loop in shell? – for {variable name} in {list} do {statement} done
  • How do you write a while loop in shell? – while {condition} do {statement} done
  • How does a case statement look in shell scripts? – case {variable} in {possible-value-1}) {statement};; {possible-value-2}) {statement};; esac
  • How do you read keyboard input in shell scripts? – read {variable-name}
  • How do you define a function in a shell script? – function-name() { #some code here return }
  • How does getopts command work? – The parameters to your script can be passed as -n 15 -x 20. Inside the script, you can iterate through the getopts array as while getopts n:x option, and the variable $option contains the value of the entered option.
  • How do you find out what’s your shell? - echo $SHELL
  • How do you fire a process in the background? -  ./process-name &
  •  How do you refer to the arguments passed to a shell script? - $1, $2 and so on. $0 is your script name.
  • What’s the conditional statement in shell scripting? - if then … fi
  • How do you do number comparison in shell scripts? - -eq, -ne, -lt, -le, -gt, -ge
  • How do you test for file properties in shell scripts? - -s filename tells you if the file is not empty, -f file…
  • How do you do Boolean logic operators in shell scripting? - ! tests for logical not, -a tests for logical and, and …
  • How do you find out the number of arguments passed to the shell script? - $#
  • What’s a way to do multilevel if-else’s in shell scripting? - if then elif fi
  • How do you write a for loop in shell? –  for in do done\
  • How do you write a while loop in shell? –  while do done
  • How does a case statement look in shell scripts? - case in ) ;; ) ;; esac
  • How do you read keyboard input in shell scripts? - read
  • How do you define a function in a shell script? - function-name()
  • How do you stop all the processes, except the shell window? - kill 0
  • How do you find out about all running processes? - ps -ag
  • How do you stop a process? - kill pid
  • What’s the command to find out today’s date? –  date
  • What’s the command to find out users on the system? - who
  • How do you find out the current directory you’re in? –  pwd
  • How do you remove a file? - rm
  • How do you remove recursively? –  rm -rf
  • How do you find out your own username? – whoami
  • How do you send a mail message to somebody? –  mail somebody@interviewduniya.com -s ‘Your subject’ -c …
  • How do you count words, lines and characters in a file? - wc
  • How do you search for a string inside a given file? –  grep string filename
  • How do you search for a string inside a directory? –  grep string *
  • How do you search for a string in a directory with the subdirectories recursed? - grep -r string *
  • What are PIDs? - They are process IDs given to processes. A PID can vary…
  • How do you list currently running process? –  ps
Shell Scripting Interview Questions
1) What is Shell Scripting?
Shell scripting is used to program command line of an operating system. Shell Scripting is also used to program the shell which is the base for any operating system. Shell scripts often refer to programming UNIX. Shell scripting is mostly used to program operating systems of windows, UNIX, Apple, etc. Also this script is used by companies to develop their own operating system with their own features.
2) State the advantages of Shell scripting?
There are many advantages of shell scripting some of them are, one can develop their own operating system with relevant features best suited to their organization than to rely on costly operating systems. Software applications can be designed according to their platform.
3) What are the disadvantages of shell scripting?
There are many disadvantages of shell scripting they are
* Design flaws can destroy the entire process and could prove a costly error.
* Typing errors during the creation can delete the entire data as well as partition data.
* Initially process is slow but can be improved.
* Portbility between different operating system is a prime concern as it is very difficult to port scripts etc.
4) Explain about the slow execution speed of shells?
Major disadvantage of using shell scripting is slow execution of the scripts. This is because for every command a new process needs to be started. This slow down can be resolved by using pipeline and filter commands. A complex script takes much longer time than a normal script.
5) Give some situations where typing error can destroy a program?
There are many situations where typing errors can prove to be a real costly effort. For example a single extra space can convert the functionality of the program from deleting the sub directories to files deletion. cp, cn, cd all resemble the same but their actual functioning is different. Misdirected > can delete your data.
Coding Related Shell Scripting Interview Questions …
6) Explain about return code?
Return code is a common feature in shell programming. These return codes indicate whether a particular program or application has succeeded or failed during its process. && can be used in return code to indicate which application needs to be executed first.
7) What are the different variables present in Linux shell?
Variables can be defined by the programmer or developer they specify the location of a particular variable in the memory. There are two types of shells they are System variables and user defined variables. System variables are defined by the system and user defined variables are to be defined by the user (small letters).
8) Explain about GUI scripting?
Graphical user interface provided the much needed thrust for controlling a computer and its applications. This form of language simplified repetitive actions. Support for different applications mostly depends upon the operating system. These interact with menus, buttons, etc.
Shell Scripting Command Interview Questions …
9) Explain about echo command?
Echo command is used to display the value of a variable. There are many different options give different outputs such as usage \c suppress a trailing line, \r returns a carriage line, -e enables interpretation, \r returns the carriage.
10) Explain about Stdin, Stdout and Stderr?
These are known as standard input, output and error. These are categorized as 0, 1 and 2. Each of these functions has a particular role and should accordingly functions for efficient output. Any mismatch among these three could result in a major failure of the shell.
11) Explain about sourcing commands?
Sourcing commands help you to execute the scripts within the scripts. For example sh command makes your program to run as a separate shell. .command makes your program to run within the shell. This is an important command for beginners and for special purposes.
12) Explain about debugging?
Shell can make your debugging process easier because it has lots of commands to perform the function. For example sh –ncommand helps you to perform debugging. It helps you to read the shell but not to execute it during the course. Similarly sh –x command helps you by displaying the arguments and functions as they are executed.
13) Explain about Login shell?
Login shell is very useful as it creates an environment which is very useful to create the default parameters. It consists of two files they are profile files and shell rc files. These files initialize the login and non login files. Environment variables are created by Login shell.
14) Explain about non-login shell files?
The non login shell files are initialized at the start and they are made to run to set up variables. Parameters and path can be set etc are some important functions. These files can be changed and also your own environment can be set. These functions are present in the root. It runs the profile each time you start the process.
15) Explain about shebang?
Shebang is nothing but a # sign followed by an exclamation. This is visible at the top of the script and it is immediately followed by an exclamation. To avoid repetitive work each time developers use shebang. After assigning the shebang work we pass info to the interpreter.
16) Explain about the Exit command?
Every program whether on UNIX or Linux should end at a certain point of time and successful completion of a program is denoted by the output 0. If the program gives an output other than 0 it defines that there has been some problem with the execution or termination of the problem. Whenever you are calling other function, exit command gets displayed.
17) Explore about Environment variables?
Environment variables are set at the login time and every shell that starts from this shell gets a copy of the variable. When we export the variable it changes from an shell variable to an environment variable and these variables are initiated at the start of the shell.
>>>……………………………………………………………………………………<<<
ls – Unix users and sysadmins cannot live without this two letter command. Whether you use it 10 times a day or 100 times a day, knowing the power of ls command can make your command line journey enjoyable.
1. Open Last Edited File Using ls -t
To open the last edited file in the current directory use the combination of ls, head and vi commands as shown below.
ls -t sorts the file by modification time, showing the last edited file first. head -1 picks up this first file.
$ vi first-long-file.txt
$ vi second-long-file.txt
$ vi `ls -t | head -1`
[Note: This will open the last file you edited (i.e second-long-file.txt)]
2. Display One File Per Line Using ls -1
To show single entry per line, use -1 option as shown below.
$ ls -1
bin
boot
cdrom
dev
etc
home
initrd
initrd.img
lib
3. Display All Information About Files/Directories Using ls -l
To show long listing information about the file/directory.
$ ls -l
-rw-r—– 1 ramesh team-dev 9275204 Jun 13 15:27 mthesaur.txt.gz
1st Character – File Type: First character specifies the type of the file.
In the example above the hyphen (-) in the 1st character indicates that this is a normal file. Following are the possible file type options in the 1st character of the ls -l output.
Field Explanation
- normal file
d directory
s socket file
l link file
Field 1 – File Permissions: Next 9 character specifies the files permission. Each 3 characters refers to the read, write, execute permissions for user, group and world In this example, -rw-r—– indicates read-write permission for user, read permission for group, and no permission for others.
Field 2 – Number of links: Second field specifies the number of links for that file. In this example, 1 indicates only one link to this file.
Field 3 – Owner: Third field specifies owner of the file. In this example, this file is owned by username ‘ramesh’.
Field 4 – Group: Fourth field specifies the group of the file. In this example, this file belongs to ”team-dev’ group.
Field 5 – Size: Fifth field specifies the size of file. In this example, ’9275204′ indicates the file size.
Field 6 – Last modified date & time: Sixth field specifies the date and time of the last modification of the file. In this example, ‘Jun 13 15:27′ specifies the last modification time of the file.
Field 7 – File name: The last field is the name of the file. In this example, the file name is mthesaur.txt.gz.
4. Display File Size in Human Readable Format Using ls -lh
Use ls -lh (h stands for human readable form), to display file size in easy to read format. i.e M for MB, K for KB, G for GB.
$ ls -l
-rw-r—– 1 ramesh team-dev 9275204 Jun 12 15:27 arch-linux.txt.gz*
$ ls -lh
-rw-r—– 1 ramesh team-dev 8.9M Jun 12 15:27 arch-linux.txt.gz
5. Display Directory Information Using ls -ld
When you use “ls -l” you will get the details of directories content. But if you want the details of directory then you can use -d option as., For example, if you use ls -l /etc will display all the files under etc directory. But, if you want to display the information about the /etc/ directory, use -ld option as shown below.
$ ls -l /etc
total 3344
-rw-r–r– 1 root root 15276 Oct 5 2004 a2ps.cfg
-rw-r–r– 1 root root 2562 Oct 5 2004 a2ps-site.cfg
drwxr-xr-x 4 root root 4096 Feb 2 2007 acpi
-rw-r–r– 1 root root 48 Feb 8 2008 adjtime
drwxr-xr-x 4 root root 4096 Feb 2 2007 alchemist
$ ls -ld /etc
drwxr-xr-x 21 root root 4096 Jun 15 07:02 /etc
6. Order Files Based on Last Modified Time Using ls -lt
To sort the file names displayed in the order of last modification time use the -t option. You will be finding it handy to use it in combination with -l option.
$ ls -lt
total 76
drwxrwxrwt 14 root root 4096 Jun 22 07:36 tmp
drwxr-xr-x 121 root root 4096 Jun 22 07:05 etc
drwxr-xr-x 13 root root 13780 Jun 22 07:04 dev
drwxr-xr-x 13 root root 4096 Jun 20 23:12 root
drwxr-xr-x 12 root root 4096 Jun 18 08:31 home
drwxr-xr-x 2 root root 4096 May 17 21:21 sbin
lrwxrwxrwx 1 root root 11 May 17 20:29 cdrom -> media/cdrom
drwx—— 2 root root 16384 May 17 20:29 lost+found
drwxr-xr-x 15 root root 4096 Jul 2 2008 var
7. Order Files Based on Last Modified Time (In Reverse Order) Using ls -ltr
To sort the file names in the last modification time in reverse order. This will be showing the last edited file in the last line which will be handy when the listing goes beyond a page. This is my default ls usage. Anytime I do ls, I always use ls -ltr as I find this very convenient.
$ ls -ltr
total 76
drwxr-xr-x 15 root root 4096 Jul 2 2008 var
drwx—— 2 root root 16384 May 17 20:29 lost+found
lrwxrwxrwx 1 root root 11 May 17 20:29 cdrom -> media/cdrom
drwxr-xr-x 2 root root 4096 May 17 21:21 sbin
drwxr-xr-x 12 root root 4096 Jun 18 08:31 home
drwxr-xr-x 13 root root 4096 Jun 20 23:12 root
drwxr-xr-x 13 root root 13780 Jun 22 07:04 dev
drwxr-xr-x 121 root root 4096 Jun 22 07:05 etc
drwxrwxrwt 14 root root 4096 Jun 22 07:36 tmp
8. Display Hidden Files Using ls -a (or) ls -A
To show all the hidden files in the directory, use ‘-a option’. Hidden files in Unix starts with ‘.’ in its file name.
$ ls -a
[rnatarajan@asp-dev ~]$ ls -a
. Debian-Info.txt
.. CentOS-Info.txt
.bash_history Fedora-Info.txt
.bash_logout .lftp
.bash_profile libiconv-1.11.tar.tar
.bashrc libssh2-0.12-1.2.el4.rf.i386.rpm
It will show all the files including the ‘.’ (current directory) and ‘..’ (parent directory). To show the hidden files, but not the ‘.’ (current directory) and ‘..’ (parent directory), use option -A.
$ ls -A
Debian-Info.txt Fedora-Info.txt
CentOS-Info.txt Red-Hat-Info.txt
.bash_history SUSE-Info.txt
.bash_logout .lftp
.bash_profile libiconv-1.11.tar.tar
.bashrc libssh2-0.12-1.2.el4.rf.i386.rpm
[Note: . and .. are not displayed here]
9. Display Files Recursively Using ls -R
$ ls /etc/sysconfig/networking
devices profiles
$ ls -R /etc/sysconfig/networking
/etc/sysconfig/networking:
devices profiles
/etc/sysconfig/networking/devices:
/etc/sysconfig/networking/profiles:
default
/etc/sysconfig/networking/profiles/default:
To show all the files recursively, use -R option. When you do this from /, it shows all the unhidden files in the whole file system recursively.
10. Display File Inode Number Using ls -i
Sometimes you may want to know the inone number of a file for internal maintenance. Use -i option as shown below to display inone number. Using inode number you can remove files that has special characters in it’s name as explained in the example#6 of the find command article.
$ ls -i /etc/xinetd.d/
279694 chargen 279724 cups-lpd 279697 daytime-udp
279695 chargen-udp 279696 daytime 279698 echo
11. Hide Control Characters Using ls -q
To print question mark instead of the non graphics control characters use the -q option.
ls -q
12. Display File UID and GID Using ls -n
Lists the output like -l, but shows the uid and gid in numeric format instead of names.
$ ls -l ~/.bash_profile
-rw-r–r– 1 ramesh ramesh 909 Feb 8 11:48 /home/ramesh/.bash_profile
$ ls -n ~/.bash_profile
-rw-r–r– 1 511 511 909 Feb 8 11:48 /home/ramesh/.bash_profile
[Note: This display 511 for uid and 511 for gid]
13. Visual Classification of Files With Special Characters Using ls -F
Instead of doing the ‘ls -l’ and then the checking for the first character to determine the type of file. You can use -F which classifies the file with different special character for different kind of files.
$ ls -F
Desktop/ Documents/ Ubuntu-App@ firstfile Music/ Public/ Templates/
Thus in the above output,
/ – directory.
nothing – normal file.
@ – link file.
* – Executable file
14. Visual Classification of Files With Colors Using ls -F
Recognizing the file type by the color in which it gets displayed is an another kind in classification of file. In the above output directories get displayed in blue, soft links get displayed in green, and ordinary files gets displayed in default color.
$ ls –color=auto
Desktop Documents Examples firstfile Music Pictures Public Templates Videos
15. Useful ls Command Aliases
You can take some required ls options in the above, and make it as aliases. We suggest the following.
Long list the file with size in human understandable form.
alias ll=”ls -lh”
Classify the file type by appending special characters.
alias lv=”ls -F”
Classify the file type by both color and special character.
alias ls=”ls -F –color=auto”
Shell Scripting Examples
1. Fibonacci Series
#!/bin/bash#!/bin/bash
# SCRIPT: fibo_iterative.sh
if [ $# -eq 1 ]
then
Num=$1
else
echo -n “Enter a Number :”
read Num
fi
f1=0
f2=1
echo “The Fibonacci sequence for the number $Num is : ”
for (( i=0; i<=Num; i++ ))
do
echo -n “$f1 ”
fn=$((f1+f2))
f1=$f2
f2=$fn
done
echo
2. Script to check all IP Pinging or not.
Ans. Write this in script.
A.
for i in `seq 1 50`
do
# set ping timeout to 2 sec
ping 192.168.0.$i 2 |grep “no answer”
done
B.
#!/bin/sh
iconpath=”/path/to/router/icon/file/internet.png”
# document icon is used, not document content
# Put the IP address of your router here
localip=192.168.1.1
clear
echo ‘Router avaiability notification with Growl’
#variable
avaiable=false
com=”################”
#comment prefix for logging porpouse
while true;
do
if $avaiable
then
echo “$com 1) $localip avaiable $com”
echo “1″
while ping -c 1 -t 2 $localip
do
sleep 5
done
growlnotify -s -I $iconpath -m “$localip is offline”
avaiable=false
else
echo “$com 2) $localip not avaiable $com”
#try to ping the router untill it come back and notify it
while !(ping -c 1 -t 2 $localip)
do
echo “$com trying…. $com”
sleep 5
done
echo “$com found $localip $com”
growlnotify -s -I $iconpath -m “$localip is online”
avaiable=true
fi
sleep 5
done
C.
set 10.0.0.1 10.0.0.2 # etc
trap exit 2
while true; do
i=1
for ipnumber in “$@”; do
statusname=up$i
laststatus=${!statusname:-0}
ping -c 1 -t 2 $ipnumber > /dev/null
ok=$?
eval $statusname=$ok
if [ ${!statusname} -ne $laststatus ]; then
echo status changed for $ipnumber
if [ $ok -eq 0 ]; then
echo now it is up
else
echo now it is down
fi
fi
i=$(($i + 1))
done
sleep 5
done
3. Shell script to monitor user directory size.
#!/bin/bash
#directory to be monitored
dir1=/raid/userhome/abc
dir2=/raid/userhome/def
dir3=/raid/userhome/ghi
dir4=/raid/userhome/jki
# limit on the size (in KB) of the directory
limit=500000
# alert email
email1=abc@domain.com
email2=def@domain.com
email3=ghi@domain.com
email4=jki@domain.com
# if directory exists, find out it’s size
if [ -d $dir ]
then
size=$(du -sk $dir | cut -f1)
else
echo “$dir is invalid !!!” | mail -s “Invalid directory” $email
exit
fi
# if directory size is greater than limit, alert the user
if [ $size -gt $limit ]
then
echo “Total amount of data in $dir is ${size}KB and exceeds the limit of $limit kB!” | mailx -s “File limit exceeded” $email
fi

Monday, May 20, 2013

mysql


Mysql, mysqli, pdo

There are (more than) three popular ways to use MySQL from PHP.

1.     The mysql functions are procedural and use manual escaping.

2.     mysqli is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements.

3.     PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned.
I would recommend using PDO with prepared statements. It is a well-designed API and will let you more easily move to another database (including any that supports ODBC) if necessary.

Store procedure:
Stored Procedures Advantages
§  Stored procedures help increase the performance of application using them. Once created, stored procedures are compiled and stored in the database catalog. A stored procedure typically runs faster than uncompiled SQL statements that are sent from external applications.
§  Stored procedures reduce the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL statements, the application only has to send the stored procedure’s name with parameters.
§  Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so developers don’t have to develop functions that are already supported in stored procedure in those applications.
§  Stored procedures are secured. Database administrator can grant appropriate permission to application that access stored procedures in database catalog without giving any permission on the underlying database tables.

Stored Procedures Disadvantages
§  Stored procedures make the database server high load in both memory and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations which is not well-designed for database server.

Creation: Update user

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`update_user`$$
CREATE PROCEDURE `test`.`update_user`
(
IN userId INT,
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
update users set first_name=firstName,last_name=lastName where users_id=userId;
END $$
DELIMITER ;

Call the store procedure with php:

$rs = $mysqli->query( 'CALL update_user(4,"Rakesh","mumtaz")' );

MySQL triggers

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either BEFORE or AFTER data change by INSERT, UPDATE and DELETE statements. MySQL allows you to define maximum six triggers for each table.
§  BEFORE INSERT
§  AFTER INSERT
§  BEFORE UPDATE
§  AFTER UPDATE
§  BEFORE DELETE
§  AFTER DELETE

Creating a Trigger

We now require two triggers:
  • When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).
  • When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set.
Note that the changetime field will automatically be set to the current time.
Each trigger requires:
  1. A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
  2. The table which triggers the event. A single trigger can only monitor a single table.
  3. When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.
  4. The trigger body; a set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers.
The basic trigger syntax is:
 
CREATE
    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
               -- trigger body
               -- this code is applied to every
               -- inserted/updated/deleted row
    END;
We require two triggers — AFTER INSERT and AFTER UPDATE on the blog table. It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true.
The first MySQL command we’ll issue is a little unusual:
 
DELIMITER $$
Our trigger body requires a number of SQL commands separated by a semi-colon (;). To create the full trigger code we must change delimiter to something else — such as $$.
Our AFTER INSERT trigger can now be defined. It determines whether the deleted flag is set, sets the @changetype variable accordingly, and inserts a new record into the audit table:
 
CREATE
        TRIGGER `blog_after_insert` AFTER INSERT
        ON `blog`
        FOR EACH ROW BEGIN
               IF NEW.deleted THEN
                       SET @changetype = 'DELETE';
               ELSE
                       SET @changetype = 'NEW';
               END IF;
               INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
    END$$
Finally, we set the delimiter back to a semi-colon:
 
DELIMITER;
The AFTER UPDATE trigger is almost identical:
 
DELIMITER $$
CREATE
        TRIGGER `blog_after_update` AFTER UPDATE
        ON `blog`
        FOR EACH ROW BEGIN
               IF NEW.deleted THEN
                       SET @changetype = 'DELETE';
               ELSE
                       SET @changetype = 'EDIT';
               END IF;
               INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
    END$$
DELIMITER ;
It’s beyond the scope of this article, but you could consider calling a single stored procedure which handles both triggers.

Trigger Happy?

Let’s see what happens when we insert a new post into our blog table:
 
INSERT INTO blog (title, content) VALUES ('Article One', 'Initial text.');
A new entry appears in the `blog` table as you’d expect:
id
title
content
deleted
1
Article One
Initial text
0
In addition, a new entry appears in our `audit` table:
id
blog_id
changetype
changetime
1
1
NEW
2011-05-20 09:00:00
Let’s update our blog text:
 
UPDATE blog SET content = 'Edited text' WHERE id = 1;
As well as changing the post, a new entry appears in the `audit` table:
id
blog_id
changetype
changetime
1
1
NEW
2011-05-20 09:00:00
2
1
EDIT
2011-05-20 09:01:00
Finally, let’s mark the post as deleted:
 
UPDATE blog SET deleted = 1 WHERE id = 1;
The `audit` table is updated accordingly and we have a record of when changes occurred:
id
blog_id
changetype
changetime
1
1
NEW
2011-05-20 09:00:00
2
1
EDIT
2011-05-20 09:01:00
3
1
DELETE
2011-05-20 09:03:00
This is a simple example but I hope it’s provided some insight into the power of MySQL triggers. In my next post we’ll implement a scheduled event to archive deleted posts.


Trigger query sql


USE [doc0nedb]
GO
/****** Object:  Trigger [dbo].[manualtbbom]    Script Date: 03/15/2013 07:50:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[manualtbbom]
   ON  [dbo].[tbBOM]
   AFTER INSERT
AS
BEGIN
DECLARE @cdpkid int
DECLARE @bomdwgnumber varchar(255)

SELECT @cdpkid = [ChildDocumentPKId],@bomdwgnumber = [BOMDwgNum] FROM INSERTED
IF(@cdpkid IS NULL OR @cdpkid = '')
BEGIN
DECLARE @childdocpkid int
DECLARE @picnumber int
DECLARE @childissue int
DECLARE @childsizes varchar(2)
DECLARE @childtypes varchar(10)
SET @childdocpkid = (SELECT PKId FROM dbo.tbstorlib WHERE DrawingID = @bomdwgnumber)
SET @picnumber = (SELECT MaterialNumber FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)
SET @childissue = (SELECT Issue FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)
SET @childsizes = (SELECT Size FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)
SET @childtypes = (SELECT DwgType FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)
UPDATE dbo.tbBOM SET ChildDocumentPKId = @childdocpkid, PicNum = @picnumber, ChildIssue = @childissue, ChildSize = @childsizes, ChildType = @childtypes WHERE BOMDwgNum = @bomdwgnumber
END
END